3.SQL Server DML触发器--检测对指定列的UPDATE或INSERT操作

时间:2021-07-16 00:31:49

本文摘自《锋利的SQL》:http://item.jd.com/10380652.html

14.1.3 检测对指定列的UPDATE或INSERT操作
对于INSERT或UPDATE触发器,可以使用UPDATE( )或COLUMNS_UPDATED( )函数来检测对列的修改,从而据以执行相应的操作。其中,UPDATE( )函数可以测试对某个列的UPDATE或INSERT尝试。COLUMNS_UPDATED( )可以测试对多个列执行的UPDATE或INSERT操作。
1.使用UPDATE( )测试指定列
下面的示例为DetailTable表定义了一个用于UPDATE操作的触发器。当修改DetailTable表中的ProductID列或ProductCount列时,将把修改前和修改后的数据复制到DetailTable1表中。
-- 创建表
CREATE TABLE DetailTable
(OrderID int, ProductID int, ProductCount int NOT NULL, Price money);
CREATE TABLE DetailTable1
(OrderID int, ProductID int, ProductCount int NOT NULL, Price money);
GO

-- 插入行
INSERT INTO DetailTable VALUES(1,1,10,1000.00);
INSERT INTO DetailTable VALUES(2,1,10,1000.00);
GO

-- 创建触发器
CREATE TRIGGER Trigger1
ON DetailTable
AFTER UPDATE
AS
IF (UPDATE(ProductID) OR UPDATE(ProductCount))
BEGIN
INSERT INTO DetailTable1
SELECT * FROM Deleted; -- 将更新前的数据复制到DetailTable1中
INSERT INTO DetailTable1
SELECT * FROM Inserted; -- 将更新后的数据复制到DetailTable1中
END

下面的UPDATE语句将DetailTable表中OrderID为1行中的ProductID修改为2(修改前的值为1)。从DetailTable1表中可以看到修改前和修改后的行都被复制了进来,如图14-2所示。
UPDATE DetailTable
SET ProductID = 2
WHERE OrderID = 1;
SELECT * FROM DetailTable1;

3.SQL Server DML触发器--检测对指定列的UPDATE或INSERT操作
图14-2 被复制到DetailTable1中的修改前和修改后的行
2.使用COLUMNS_UPDATED( )测试多个列
COLUMNS_UPDATED( )可以针对多列执行的UPDATE或INSERT操作的进行测试。COLUMNS_UPDATED返回一个或多个从左至右排序的字节。表中的第1列由最左侧字节的最右侧位(即最低位)表示;第2列由向左的下一位表示,依此类推。如果创建了触发器的表包含八列以上,则COLUMNS_UPDATED( )返回多个字节。返回的字节数与表中的列数有关,而与更新的列数无关。
例如,假设T1表包含有10个列,分别是C1、C2…C9和C10。假设对表中的C2、C4和C9列进行了更新,由于T1表超过了8列,所以COLUMNS_UPDATED( )将返回两个字节,以满足表达位值的要求,如图14-3所示。
3.SQL Server DML触发器--检测对指定列的UPDATE或INSERT操作
图14-3 更新C2、C4、C9列时返回的位值
下面的示例为T1表定义了一个用于UPDATE的触发器,当更新表中的C2、C4和C6列时,将返回提示消息。判断修改的方法是通过计算COLUMNS_UPDATED( )返回的字节值实现的。例如,在C2、C4和C6列均被修改的情况下,COLUMNS_UPDATED( )返回字节中的二进制位是“00101010”,转换为十进制应当是25+23+21=42。
USE AdventureWorks;
Go

-- 创建表
CREATE TABLE T1
(C1 int,
C2 int,
C3 int,
C4 int,
C5 int,
C6 int,
C7 int);

-- 插入行
INSERT INTO T1 VALUES(1, 1, 1, 1, 1, 1, 1);
GO

-- 创建触发器
CREATE TRIGGER Trigger1
ON T1
AFTER UPDATE
AS
IF (COLUMNS_UPDATED() = POWER(2,(2-1)) + POWER(2,(4-1)) + POWER(2,(6-1)))
PRINT N'修改了第2、4、6列';

GO

-- 测试触发器
UPDATE T1
SET C2 = 0, C4 = 0, C6 =0
WHERE C1 = 1
如果表中包含的列数超过了8列,COLUMNS_UPDATED将返回多个字节,这时候应当对字节进行截取,以判断每个字节中二进制位的情况。例如,下面示例中的T1表包含有10列,所定义的触发器在修改表中C2、C4和C10列时,将返回提示消息。
USE AdventureWorks;
Go

-- 创建表
CREATE TABLE T1
(C1 int,
C2 int,
C3 int,
C4 int,
C5 int,
C6 int,
C7 int,
C8 int,
C9 int,
C10 int);

-- 插入行
INSERT INTO T1 VALUES(1, 1, 1, 1, 1, 1, 1, 1,1, 1);
GO

-- 创建触发器
CREATE TRIGGER Trigger1
ON T1
AFTER UPDATE
AS
DECLARE @colval binary(2); -- 定义变量存储COLUMNS_UPDATED()的返回值
SET @colval = COLUMNS_UPDATED();
IF (SUBSTRING(@colval,1,1) = POWER(2,(2-1)) + POWER(2,(4-1)) -- 判断存储在第1个字节中的表的第2列和第4列
AND SUBSTRING(@colval,2,1) = POWER(2,(2-1))) -- 判断存储在第2个字节中的表的第10列
PRINT N'修改了第2、4、10列';

GO

-- 测试触发器
UPDATE T1
SET C2 = 0, C4 = 0, C10 = 0
WHERE C1 =1