本文详细讲述了Oracle中对COLUMNS_UPDATED()返回值解析
需求来源
客户要求[某些特定的表]能[自定义预警报告]
(在特定的表上)用户可定义某些字段有修改时向有关用户发出消息警报<内容大致是 xx 单据的 xx 单号的xx字段由 old 变为了 new>最终目的是由消息控制模块向消息接收人报告这一变更
基础知识:
COLUMNS_UPDATED()是一个仅可在 Insert or Update trigger 中调用的方法
该方法返回 一个 varbinary 的值 存储了当次Insert 或是Update 触发器所对应的记录在哪些字段上发生了Inserted or updated在SQLSERVER 的联机帮助[CREATE TRIGGER]和[IF UPDATE] 中有对 COLUMNS_UPDATED () 方法的简要描述
公司要求用Trigger 实现: (为每个[特定的表]编写一个特定的UPDATE 触发器)主要难点是穷举IF UPDATE(column)的方法不可行其它业务实现无问题后来仔细琢磨COLUMNS_UPDATED() 所返回的值问题得到解决
这里只是讲述对COLUMNS_UPDATED()所返回的值的解析和运用就不考虑用户指定变更字段及插入记录到消息表的那部分实现过程了
测试数据准备
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( ) Primary Keyf_charChar() default f_varchar varchar() default f_nvarchar nvarchar() default f_datetime datetime default getdate()f_intint default f_bigint bigint default f_decimal decimal( ) default f_number numeric( ) default f_floatfloat default )goINSERT INTO T_Test (f_char) values()INSERT INTO T_Test (f_char) values() go
编写Update 触发器
If exists(select * from sysobjects where id=object_id(N[dbo][Tri_Test_Upd]) and objectproperty(idNistrigger)=)DROP TRIGGER Tri_Test_UpdgoCREATE TRIGGER Tri_Test_Upd ON T_Test WITH ENCRYPTIONFOR UPDATEASDECLARE @iRowCnt INTSET @iRowCnt = @@rowcountIF @iRowCnt < RETURNDECLARE@sTableVARCHAR()@sPKName VARCHAR()@sColName VARCHAR()DECLARE@iColCntINT@iColIdINTDECLARE@iTINYINT@jTINYINT@iSegment TINYINT@iVal TINYINT@iLogTINYINTDECLARE@sSQLVARCHAR()SET @sTable = t_testSET @sPKName = f_id 求得当前表列个数SELECT @iColCnt = Count() FROM syscolumns WHERE id = object_id(@sTable) 以 个字段为一小段SET @iSegment = CASE WHEN @iColCnt / = @iColCnt / THEN @iColCnt / ELSE @iColCnt / + END 将数据存入 临时表SELECT * INTO #Inserted FROM InsertedSELECT * INTO #Deleted FROM Deleted 中间处理数据用CREATE TABLE #Temp(f_PKValvarchar() not null primary keyf_OldValvarchar()f_NewValvarchar())SET @i = WHILE @i < @iSegment BEGIN IF @iColCnt < SET @iVal= COLUMNS_UPDATED() ELSESET @iVal= SubString(COLUMNS_UPDATED() @i + ) 等于 则表示当前小节所对应的个字段无一被改 IF @iVal = BEGINSET @i = @i + CONTINUEEND WHILE @iVal > BEGINSET @j = SET @iLog = @iVal / WHILE @iLog > BEGIN SET @j = @j + SET @iLog = @iLog / END 得到被Update 的 列IDSET @iColId = * @i + @j + 将Update列名 赋予 @sColNameSELECT @sColName = Sname FROM Inserted as I Deleted as D Syscolumns as SWHERE IF_id = DF_id AND Sid = object_id(@sTable) AND lid = @iColIdTruncate table #Temp 拼成动态语句SET @sSQL = INSERT INTO #Temp (f_PkVal f_OldVal f_NewVal) + SELECT Convert( varchar() I + @sPkName + ) + Convert( varchar() D + @sColName + ) + Convert( varchar() I + @sColName + ) + FROM#Inserted as I #Deleted as D + WHERE I + @sPKName + = D + @sPKName + AND I + @sColName + <> D + @sColNameEXEC(@sSQL) 测试输出 Select f_pkVal@sColName as f_column_name f_oldVal f_newValFROM #temp 实际上用 将信息处理后插入消息表/*INSERT INTO T_Message() SELECT 要组织的内容FROM #temp*/SET @iVal = @iVal Power( @j)END SET @i = @i + ENDDROP TABLE #InsertedDROP TABLE #DeletedDROP TABLE #Temp go
测试数据
Update T_test Set f_datetime = getdate() f_float = f_int= 上面Update 语句共修改了三个列实际输出) f_int f_int ) f_datetime May :PM May :PM f_datetime May :PM May :PM) f_float f_float
算法
COLUMNS_UPDATED()方法返回的 varbinary是以每个小节存储个字段(的修改状态)的方式记录了当前触发器所有列的修改情形因此程序以个字段为一片段来循环处理所有字段
SET @iVal= SubString(COLUMNS_UPDATED() @i + )程序用上面语句将一小节转化为整型测试发现:（当且谨当这一小片只有一个字段有修改时）
@iVal = = ^();@iVal = = ^();@iVal = = ^();@iVal = = ^();@iVal = = ^();@iVal = = ^();@iVal = = ^(); @iVal = = ^();
而当且谨当个字段有修改时:
@iVal = ^() + ^() = ;
而第 三个字段有修改时:
@iVal = ^() + ^() + ^() = ;
当个字段都有修改时:
@iVal = ^() + ^() + + ^() = ;
也就是说 无论怎样修改@iVal的值不外乎是^n (n> and n < int)这一数组型成的[和组合](组合时每个数组成员最多出现一次)因此反过来推算: 对 @iVal 按 ^n分解 就可算得被修改列的列表
