深入理解SQL Server 2005 中的 COLUMNS_UPDATED函数

概述:

COLUMNS_UPDATED函数能够出现在INSERT或UPDATE触发器中AS关键字后的任何位置，用来指示表或视图中有哪些列已被插入或者更新。它通常和IF语句一起使用，从而可以根据不同的结果，促使触发器执行不同的操作。因此在DML触发器中，COLUMNS_UPDATED函数是一个非常重要且有用的函数。
不同于UPDATE函数，COLUMNS_UPDATED函数可以工作在多个列中,它使用字节中的位（Bit）标识列是否已被修改（也就是采用二进制的方式），而不是在参数中传递列名。我们都知道，1字节（BYTE）=8比特(Bit)，因此仅仅使用COLUMNS_UPDATED函数，则只能够标识一个表或视图的前八列是否被修改。如果表的列多于八列，那么必须和SUBSTRING函数一起搭配使用，这将在后文中进行详细讨论。
 

对COLUMNS_UPDATED函数的原理分析:

COLUMNS_UPDATED函数返回类型为varbinary，这是一种二进制类型，它可以表示一个或多个字节，用来映射相关联表的列。因此COLUMNS_UPDATED函数返回一个或多个由左向右排序的字节，这取决于要验证更新的表是否拥有八个以上的列。最左侧字节表示列的序号为1至8的列，越偏向右侧的字节，其所代表的列的序号也就越大。而列序号的排序是在创建表时，由列被定义的顺序所决定的。在每个字节的内部，越偏向左侧，列的序号越大，而左侧字节所代表的列的序号总小于右侧字节的。请允许我用一个简单的示例来阐述上述过程：
我们假设有一张表TriggerDemo，它拥有24个列，分别为COL1、COL2、COL3直至COL24。那么COLUMNS_UPDATED函数就会返回3个字节，在最左侧的字节内由右向左表示COL1至COL8，中间的字节内由右向左表示COL9至COL16，最右侧的字节内由右向左表示COL17至COL24。即（8~1 16~9 24~17）
正如我之前所说，1字节中的8比特就可以表示一张表的前八列，如果某一位为1，那么它所表示的列被标记为已更新。反之为0，那么就是未更新。如果多个列被更新，那么就会存在有多个位被设置为1的情况。例如00001010就表示第二列和第四列已经被更新。
 

使用COLUMNS_UPDATED函数测试前八列:

对于位（Bit）要说的是，第一个位称为Bit0（而不是Bit1），因此1个字节是由Bit0至Bit7共八个位所组成的。有几个列被更新，COLUMNS_UPDATED函数就会返回一个所对应的位设置为1的字节。如果我们要想知道第二列和第四列是否被更新，就应该构造出一个字节，并将Bit1和Bit3设置为1（00001010），然后需要将这个字节转换成十进制10，再和COLUMNS_UPDATED函数返回的字节进行按位逻辑与（&）操作，&运算的结果如果是10，就能够保证第二列和第四列已经同时被更新。再结合使用不同的比较运算符（<、>、=），处理各种各样的可能性，你就会真切体会到COLUMNS_UPDATED函数的功能是多么的强大了。好了，为了便于理解，请看一个完整的代码演示：

CREATE TRIGGER updDataTrigger
ON TriggerDemo
AFTER UPDATE AS
--测试第二列和第四列是否被同时更新
IF COLUMNS_UPDATED() & 10 =10
BEGIN
…………
END
--是否第二列或者第四列或者第二列和第四列同时被更新
ELSE IF COLUMNS_UPDATED() & 10 > 0 AND COLUMNS_UPDATED()&10<=10
BEGIN
…………
END
--测试第二列或者第四列是否已被更新
ELSE IF COLUMNS_UPDATED() & 10 <10 AND COLUMNS_UPDATED() & 10>0
BEGIN
…………
END

使用COLUMNS_UPDATED函数测试八个以上的列:
一旦表拥有八个以上的列，COLUMNS_UPDATED函数将会返回多个字节。这时我们就需要借助SUBSTRING函数来截取一个特定的字节，它可以从COLUMNS_UPDATED函数返回的多个字节中任意截取一个我们所想要的。
还记得我们前面的那张虚拟表TriggerDemo吗？(就是拥有24个列的那个) 如果我们想测试它的第二列、第十列以及第二十四列是否已被更新，我们就需要使用SUBSTRING函数来截取到分别代表这些列的字节，再重复前面测试前八列方法的步骤。我们参见一下代码：

哈哈 KK

个人添加的说明：【哈哈 kk】

--测试第二列、第十列以及第二十四列是否同时被更新
IF(SUBSTRING(COLUMNS_UPDATED(),1,1)&2=2)
AND (SUBSTRING(COLUMNS_UPDATED(),2,1)&2=2)
AND(SUBSTRING(COLUMNS_UPDATED(),3,1)&128=128)
BEGIN
…………
END

/*

简单地说：(注意 位数与列的对应  8~1 16~9 24~17 )
1~8列属于第一个字节，取第一个字节 SUBSTRING(COLUMNS_UPDATED(),1,1)
第二列更新则第二位为1，即 00000010，第二列简写为10，十进制为2
按位计算：
因此：SUBSTRING(COLUMNS_UPDATED(),1,1)&2=2
即为：00000010 & 10 = 10
若相等则表示第二列被更新。

9~16列属于第二个字节，取第二个字节 SUBSTRING(COLUMNS_UPDATED(),2,1)
第十列更新则第十位为1，即 00000010，第十列简写为10，十进制为2
按位计算：
因此：SUBSTRING(COLUMNS_UPDATED(),2,1)&2=2
即为：00000010 & 10 = 10
若相等则表示第十列被更新。

17~24列属于第三个字节，取第三个字节 SUBSTRING(COLUMNS_UPDATED(),3,1)
第二十四列更新则第二十四位为1，即 10000000，第二十四列十进制为128
按位计算：
因此：SUBSTRING(COLUMNS_UPDATED(),3,1)&128=128
即为：10000000 & 128 = 128
若相等则表示第二十四列被更新。

*/


函数 COLUMNS_UPDATED() 在 数据库同步的时候会用到