本文具體講述了Oracle中對(duì)COLUMNS_UPDATED()返回值解析。 需求來(lái)源 客戶(hù)要求[某些特定的表]能[自定義預(yù)警報(bào)告]。 (在特定的表上)用戶(hù)可定義某些字段有修改時(shí)，向有關(guān)用戶(hù)發(fā)出消息警報(bào)<內(nèi)容大致是 xx 單據(jù)的 xx 單號(hào)的xx字段由 old 變?yōu)榱?new>。最終目的是由消息控制模塊向消息接收人報(bào)告這一變更。 基礎(chǔ)知識(shí): COLUMNS_UPDATED()是一個(gè)僅可在 Insert or Update trigger 中調(diào)用的方法。 該方法返回 一個(gè) varbinary 的值, 存儲(chǔ)了當(dāng)次Insert 或是Update 觸發(fā)器所對(duì)應(yīng)的記錄在哪些字段上發(fā)生了Inserted or updated。在SQLSERVER 的聯(lián)機(jī)幫助[CREATE TRIGGER]和[IF UPDATE] 中有對(duì) COLUMNS_UPDATED () 方法的簡(jiǎn)要描述。 公司要求用Trigger 實(shí)現(xiàn): (為每個(gè)[特定的表]編寫(xiě)一個(gè)特定的UPDATE 觸發(fā)器。)主要難點(diǎn)是窮舉IF UPDATE(column)的方法不可行。其它業(yè)務(wù)實(shí)現(xiàn)無(wú)問(wèn)題。后來(lái)仔細(xì)琢磨COLUMNS_UPDATED() 所返回的值,問(wèn)題得到解決。 這里只是講述對(duì)COLUMNS_UPDATED()所返回的值的解析和運(yùn)用，就不考慮用戶(hù)指定變更字段及插入記錄到消息表的那部分實(shí)現(xiàn)過(guò)程了。 測(cè)試數(shù)據(jù)預(yù)備 If exists(select * from sysobjects where id=object_id(N'[dbo].[T_Test]') and xtype = 'u')DROP Table T_TestgoCREATE Table T_Test (f_idint IDENTITY(1, 1) Primary Key,f_charChar(8) default '',f_varchar　 varchar(8) default '',f_nvarchar　 nvarchar(8) default '',f_datetime　 datetime default getdate(),f_intint default 0,f_bigint　 bigint default 0,f_decimal　 decimal(18, 6) default 0.00,f_number　 numeric(18, 6) default 0.00,f_floatfloat default 0.00)goINSERT INTO T_Test (f_char) values('001')INSERT INTO T_Test (f_char) values('002') go ; 編寫(xiě)Update 觸發(fā)器 If exists(select * from sysobjects where id=object_id(N'[dbo].[Tri_Test_Upd]') and objectproperty(id,N'istrigger')=1)DROP TRIGGER Tri_Test_UpdgoCREATE TRIGGER Tri_Test_Upd ON T_Test --WITH ENCRYPTIONFOR UPDATEASDECLARE @iRowCnt INTSET @iRowCnt = @@rowcountIF @iRowCnt < 1 RETURNDECLARE　@sTable　VARCHAR(128),　@sPKName VARCHAR(32),　@sColName VARCHAR(128)DECLARE　@iColCnt　INT,　@iColId　INTDECLARE　@iTINYINT,　@jTINYINT,　@iSegment TINYINT,　@iVal　 TINYINT,　@iLog2　TINYINTDECLARE　@sSQL　VARCHAR(8000)SET @sTable = 't_test'SET @sPKName = 'f_id'-- 求得當(dāng)前表列個(gè)數(shù)SELECT @iColCnt = Count(1) FROM syscolumns WHERE id = object_id(@sTable)-- 以8 個(gè)字段為一小段SET @iSegment = CASE　 WHEN @iColCnt / 8 = @iColCnt / 8.0THEN @iColCnt / 8ELSE @iColCnt / 8 + 1END-- 將數(shù)據(jù)存入 臨時(shí)表SELECT * INTO #Inserted FROM InsertedSELECT * INTO #Deleted FROM Deleted-- 中間處理數(shù)據(jù)用CREATE TABLE #Temp(f_PKVal　varchar(254) not null primary key,f_OldVal　varchar(254),f_NewVal　varchar(254))SET @i = 0WHILE @i < @iSegment BEGIN IF @iColCnt < 9　SET @iVal= COLUMNS_UPDATED() ELSE　SET @iVal= SubString(COLUMNS_UPDATED(), @i + 1, 1) -- 等于0, 則表示當(dāng)前小節(jié)所對(duì)應(yīng)的8個(gè)字段無(wú)一被改. IF @iVal = 0　BEGIN　SET @i = @i + 1　CONTINUE　END WHILE @iVal > 0　BEGIN　SET @j = 0　SET @iLog2 = @iVal / 2　WHILE @iLog2 > 0　 BEGIN　 SET @j = @j + 1　 SET @iLog2 = @iLog2 / 2　 END　-- 得到被Update 的 列ID　SET @iColId = 8 * @i + @j + 1　-- 將Update列名 賦予 @sColName　SELECT @sColName = S.name 　 FROM Inserted as I, Deleted as D, Syscolumns as S　WHERE I.F_id = D.F_id　 AND S.id = object_id(@sTable)　 AND S.colid = @iColId　Truncate table #Temp　-- 拼成動(dòng)態(tài)語(yǔ)句　SET @sSQL = 'INSERT INTO #Temp (f_PkVal, f_OldVal, f_NewVal) ' + 　 'SELECT Convert( varchar(200), I.' + @sPkName + '), ' + 　 'Convert( varchar(200), D.' + @sColName + '), ' + 　 'Convert( varchar(200), I.' + @sColName + ') ' + 'FROM　#Inserted as I, #Deleted as D ' + 'WHERE I.' + @sPKName + ' = D.' + @sPKName + ' AND I.' + @sColName + ' <> D.' + @sColName　EXEC(@sSQL)　-- 測(cè)試輸出　 Select f_pkVal,　@sColName as f_column_name, f_oldVal, f_newVal　FROM #temp　-- 實(shí)際上用 將信息處理后插入消息表　/*　.....　INSERT INTO T_Message(....)　 SELECT 要組織的內(nèi)容FROM #temp　*/　SET @iVal = @iVal - Power(2, @j)　END SET @i = @i + 1 ENDDROP TABLE #InsertedDROP TABLE #DeletedDROP TABLE #Temp go ; 測(cè)試數(shù)據(jù) Update T_test Set f_datetime = getdate(), f_float = 0.0123, f_int= 1--　上面Update 語(yǔ)句共修改了三個(gè)列--　實(shí)際輸出1.)1 f_int 0 12 f_int 0 12.)1 f_datetime May 15 2004　5:30PM May 15 2004　5:31PM2 f_datetime May 15 2004　5:30PM May 15 2004　5:31PM3.)1 f_float 0 0.0123 2 f_float 0 0.0123 算法 COLUMNS_UPDATED()方法返回的 varbinary，是以每個(gè)小節(jié)存儲(chǔ)8個(gè)字段(的修改狀態(tài))的方式記錄了當(dāng)前觸發(fā)器所有列的修改情形。因此程序以8個(gè)字段為一片段來(lái)循環(huán)處理所有字段。 SET @iVal= SubString(COLUMNS_UPDATED(), @i + 1, 1)程序用上面語(yǔ)句將一小節(jié)轉(zhuǎn)化為整型，測(cè)試發(fā)現(xiàn):（當(dāng)且謹(jǐn)當(dāng)這一小片只有一個(gè)字段有修改時(shí)）： 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); 而當(dāng)且謹(jǐn)當(dāng)1,2個(gè)字段有修改時(shí): @iVal = 2^(1-1) + 2^(2-1) = 3; 而第 2,5,8 三個(gè)字段有修改時(shí): @iVal = 2^(2-1) + 2^(5-1) + 2^(8-1) = 146; ... 當(dāng)8個(gè)字段都有修改時(shí): @iVal = 2^(1-1) + 2^(2-1) + ... + 2^(8-1) = 255; 也就是說(shuō) 無(wú)論怎樣修改,@iVal的值，不外乎是2^n - 1(n>0 and n <9, int)這一數(shù)組型成的[和組合](組合時(shí)每個(gè)數(shù)組成員最多出現(xiàn)一次)。因此反過(guò)來(lái)推算: 對(duì) @iVal 按 2^n分解, 就可算得被修改列的列表。