对COLUMNS_UPDATED()值的解析
是一个仅可在 Insert or Update trigger 中调用的方法.
该方法返回 一个 varbinary 的值, 存储了当次Insert 或是 Update 触发器所对应的记录在哪些字段上发生了Inserted or updated.
该方法返回 一个 varbinary 的值, 存储了当次Insert 或是 Update 触发器所对应的记录在哪些字段上发生了Inserted or updated.
COLUMNS_UPDATED 函数以从左到右的顺序返回位,最左边的为最不重要的位。最左边的位表示表中的第一列;向右的下一位表示第二列,依此类推。如果在表上创建的触发器包含 8 列以上,则 COLUMNS_UPDATED 返回多个字节,最左边的为最不重要的字节。在 INSERT 操作中 COLUMNS_UPDATED 将对所有列返回 TRUE 值,因为这些列插入了显式值或隐性 (NULL) 值。
可以在触发器主体中的任意位置使用 COLUMNS_UPDATED。
COLUMNS_UPDATED返回值varbinary的算法:
COLUMNS_UPDATED()方法返回的 varbinary,是以每个小节存储
8个字段(的修改状态)的方式记录了当前触发器所有列的修改情形.
因此程序以8个字段为一片段来循环处理所有字段.
SET @iVal= SubString(COLUMNS_UPDATED(), @i + 1, 1)
其中@i:
如果前8列,@i =1
9-16列,@i =2
17-24列,@i = 3
以次类推
程序用上面语句将一小节转化为整型.
测试发现:
当@i=1:
当且仅当这一小片只有一个字段有修改时
1,@iVal = 1 = 2^(1-1);
2,@iVal = 2 = 2^(2-1);
3,@iVal = 4 = 2^(3-1);
4,@iVal = 8 = 2^(4-11);
5,@iVal = 16 = 2^(5-1);
6,@iVal = 32 = 2^(6-1);
7,@iVal = 64 = 2^(7-1);
8,@iVal = 128 = 2^(8-1);
而当且仅当1,2个字段有修改时:
@iVal = 2^(1-1) + 2^(2-1) = 3;
而第 2,5,8 三个字段有修改时:
@iVal = 2^(2-1) + 2^(5-1) + 2^(8-1) = 146;
...
当8个字段都有修改时:
@iVal = 2^(1-1) + 2^(2-1) + ... + 2^(8-1) = 255;
也就是说 无论怎样修改,@iVal的值,不外乎是2^n - 1(n>0 and n <9, int)这一数组型成的[和组合](组合时每个数组成员最多出现一次).
因此反过来推算: 对 @iVal 按 2^n分解, 就可算得被修改列的列表.
当@i>1时:
算法跟@i=1时一样,如:
第9列=第1列
第10列=第2列
....
以次类推
用法:
IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [ ...n ]
{ comparison_operator } column_bitmask [ ...n ]
其中:
bitwise_operator
是用于比较运算的位运算符。
updated_bitmask
是整型位掩码,表示实际更新或插入的列。例如,表 t1 包含列 C1、C2、C3、C4 和 C5。
假定表 t1 上有 UPDATE 触发器,若要检查列 C2、C3 和 C4 是否都有更新,指定值 14;
若要检查是否只有列 C2 有更新,指定值 2。
comparison_operator
是比较运算符。使用等号 (=) 检查 updated_bitmask 中指定的所有列是否都实际进行了更新。
使用大于号 (>) 检查 updated_bitmask 中指定的任一列或某些列是否已更新。
column_bitmask
是要检查的列的整型位掩码,用来检查是否已更新或插入了这些列。
举例:
下例测试影响 Northwind.dbo.Customers 表中的第 3、第 5 或第 9 列的更新。
USE Northwind
DROP TRIGGER tr1
GO
CREATE TRIGGER tr1 ON Customers
FOR UPDATE AS
IF ( (SUBSTRING(COLUMNS_UPDATED(),1,1)=power(2,(3-1))
+ power(2,(5-1)))
AND (SUBSTRING(COLUMNS_UPDATED(),2,1)=power(2,(1-1)))
)
PRINT 'Columns 3, 5 and 9 updated'
GO
UPDATE Customers
SET ContactName=ContactName,
Address=Address,
Country=Country
GO