关于COLUMNS_UPDATED ( insie microsoft sql server 2008 T-sql programming 笔记) demo
上一遍是讲原理的.原理遍链接
大家可以发现,
-------------------------------------------------------
当只有8列时, 如果更新第5列, 它的16进制是 COLUMNS_UPDATED 值为 0x10 二进制是00010000
当有9列时, 如果更新第5列, 它的16进制是 COLUMNS_UPDATED 值为 0x1000 二进制是0001000000000000
在超过8列时,使用 substring
现在说一下函数 substring(0x1000,1,1),请大家不要误认为是这是字符串的截去, (取前8个字长) 它等于 0x10 , 它是第五列, 我们 用一个2^(5-1) 的数和它求与.若大于0,则满足此更新列.
00010000 & 16 即
00010000 (0x10)
& 0001000 (2^((5-1)%8 ))
--------------------------------
= 1 即>0
如果更新第9列. 它的16进数是0x0001 二进制是 0000000000000001 我们就要substring(0x1000,2,1), 取第二个8个字长的即(00000001)舍去前面8个0,它等于0x01
那么,我用于 2^((9-1)/8 ) =1
00000001 0x01
&00000001 2^((9-1)%8 )
---------------------------------------------------
= 1 即>0
ok 说到这我总结一下,就是 substring(16进制,(n-1)/8+1,1) 然后再去 与 power(2, (n-1)%8) 作"与""运算
我们知道以后, 我们先创建一个保存1,2,3,4,5................的表.
IF OBJECT_ID('dbo.nums', 'U') IS NOT NULL DROP TABLE dbo.nums;select ROW_NUMBER() over (order by (select null)) as n into Nums from sys.all_columnsselect * from Nums
接下来的代码,是出自sql server 2008 t-sql programming 第四章 159 页, 作者<本阿甘>
IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;GODECLARE @cmd AS NVARCHAR(4000), @i AS INT;SET @cmd =N'CREATE TABLE dbo.T1(keycol INT NOT NULL IDENTITY PRIMARY KEY';SET @i = 1;WHILE @i <= 100BEGINSET @cmd =@cmd + N',col' + CAST(@i AS nvarchar(10)) +N' INT NOT NULL DEFAULT 0';SET @i = @i + 1;ENDSET @cmd = @cmd + N');'EXEC sp_executesql @cmd;INSERT INTO dbo.T1 DEFAULT VALUES;SELECT * FROM T1; 
这是新建一百多列的字段表,下面新触发器
CREATE TRIGGER trg_T1_u_identify_updated_columns ON dbo.T1 FOR UPDATEASSET NOCOUNT ON;DECLARE @parent_object_id AS INT =(SELECT parent_object_idFROM sys.objectsWHERE object_id = @@PROCID);WITH UpdatedColumns(column_id) AS(SELECT nFROM dbo.NumsWHERE n <=-- count of columns in trigger's parent table(SELECT COUNT(*)FROM sys.columnsWHERE object_id = @parent_object_id)-- bit corresponding to nth column is turned onAND (SUBSTRING(COLUMNS_UPDATED(),(n - 1) / 8 + 1, 1))& POWER(2, (n - 1) % 8) > 0)SELECT C.name AS updated_columnFROM sys.columns AS CJOIN UpdatedColumns AS UON C.column_id = U.column_idWHERE object_id = @parent_object_idORDER BY U.column_id;
建完以后,测试一下
UPDATE dbo.T1SET col4 = 2, col8 = 2, col90 = 2, col6 = 2WHERE keycol = 1;
你们看,它能知道哪个字段更新了.