對COLUMNS_UPDATED()返回值的解析

原创 2004年05月23日 16:19:00

需求來源:
客戶要求[某些特定的表]能[自定義預警報告].

(在特定的表上)用戶可定義某些字段有修改時,向有關用戶發出消息警報<內容大致是 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_Test
go

CREATE Table T_Test (
f_id    int IDENTITY(1, 1) Primary Key,
f_char    Char(8) default '',
f_varchar   varchar(8) default '',
f_nvarchar   nvarchar(8) default '',
f_datetime   datetime default getdate(),
f_int    int default 0,
f_bigint   bigint default 0,
f_decimal   decimal(18, 6) default 0.00,
f_number   numeric(18, 6) default 0.00,
f_float    float default 0.00
)
go

INSERT INTO T_Test (f_char) values('001')
INSERT INTO T_Test (f_char) values('002')
go

--編寫Update 觸發器

If exists(select * from sysobjects where id=object_id(N'[dbo].[Tri_Test_Upd]') and objectproperty(id,N'istrigger')=1)
    DROP TRIGGER Tri_Test_Upd
go

CREATE TRIGGER Tri_Test_Upd ON T_Test --WITH ENCRYPTION
FOR UPDATE
AS
DECLARE @iRowCnt INT

SET @iRowCnt = @@rowcount

IF @iRowCnt < 1
 RETURN

DECLARE
  @sTable  VARCHAR(128),
  @sPKName VARCHAR(32),
  @sColName VARCHAR(128)

DECLARE
  @iColCnt  INT,
  @iColId  INT

DECLARE
  @i    TINYINT,
  @j    TINYINT,
  @iSegment TINYINT,
  @iVal   TINYINT,
  @iLog2  TINYINT

DECLARE
  @sSQL  VARCHAR(8000)

SET @sTable = 't_test'
SET @sPKName = 'f_id'

-- 求得當前表列個數
SELECT @iColCnt = Count(1) FROM syscolumns WHERE id = object_id(@sTable)

-- 以8 個字段為一小段
SET @iSegment = CASE
       WHEN @iColCnt / 8 = @iColCnt / 8.0
        THEN
         @iColCnt / 8
        ELSE
         @iColCnt / 8 + 1
        END
-- 將數據存入 臨時表
SELECT * INTO #Inserted FROM Inserted
SELECT * INTO #Deleted FROM Deleted

-- 中間處理數據用
CREATE TABLE #Temp(
f_PKVal  varchar(254) not null primary key,
f_OldVal  varchar(254),
f_NewVal  varchar(254)
)

SET @i = 0

WHILE @i < @iSegment
 BEGIN
 IF @iColCnt < 9
  SET @iVal= COLUMNS_UPDATED()
 ELSE
  SET @iVal= SubString(COLUMNS_UPDATED(), @i + 1, 1)

 -- 等於0, 則表示當前小節所對應的8個字段無一被改.
 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
  -- 拼成動態語句
  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)

  -- 測試輸出
   Select f_pkVal,  @sColName as f_column_name, f_oldVal, f_newVal  FROM #temp
  -- 實際上用 將信息處理后插入消息表
  /*
  .....
  
  INSERT INTO T_Message(....)
   SELECT 要組織的內容
    FROM #temp
  */

  SET @iVal = @iVal - Power(2, @j)
  END

 SET @i = @i + 1
 END

DROP TABLE #Inserted
DROP TABLE #Deleted
DROP TABLE #Temp

go

--  測試數據
Update T_test Set f_datetime = getdate(), f_float = 0.0123, f_int= 1

--  上面Update 語句共修改了三個列
--  實際輸出
1.)
1 f_int 0 1
2 f_int 0 1
2.)
1 f_datetime May 15 2004  5:30PM May 15 2004  5:31PM
2 f_datetime May 15 2004  5:30PM May 15 2004  5:31PM
3.)
1 f_float 0 0.0123
2 f_float 0 0.0123


--  算法

COLUMNS_UPDATED()方法返回的 varbinary,是以每個小節存儲8個字段(的修改狀態)的方式記錄了當前觸發器所有列的修改情形.因此程序以8個字段為一片段來循環處理所有字段.

SET @iVal= SubString(COLUMNS_UPDATED(), @i + 1, 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分解, 就可算得被修改列的列表.

至此, 完成了整個Trigger 的算法.


对COLUMNS_UPDATED()返回值的解析

  • zgqtxwd
  • zgqtxwd
  • 2008年04月27日 07:31
  • 222

MSSQL COLUMNS_UPDATED()值的解析

对COLUMNS_UPDATED()值的解析 是一个仅可在 Insert or Update trigger 中调用的方法.该方法返回 一个 varbinary 的值, 存储了当次Insert 或是 ...
  • wufeng4552
  • wufeng4552
  • 2009年09月29日 14:23
  • 1385

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

COLUMNS_UPDATED函数能够出现在INSERT或UPDATE触发器中AS关键字后的任何位置,用来指示表或视图中有哪些列已被插入或者更新。它通常和IF语句一起使用,从而可以根据不同的结果,促使...
  • kk185800961
  • kk185800961
  • 2014年05月20日 11:09
  • 1486

解SQL columns_updated() 函数

COLUMNS_UPDATED函数能够出现在INSERT或UPDATE触发器中AS关键字后的任何位置,用来指示表或视图中有哪些列已被插入或者更新。它通常和IF语句一起使用,从而可以根据不同的结果,促使...
  • shangdaoxi
  • shangdaoxi
  • 2010年08月06日 14:57
  • 1080

摘自:Kevin的博客(WTP SVN PO.PO.PO SPRY TimeStamp JSTL)

WTP中无法使用SVN?在Eclipse中,选择window->preferences->general->appearance->label decorations,将SVN打勾即可。 解决HIBE...
  • vivi_peng
  • vivi_peng
  • 2009年02月12日 13:33
  • 8092

[触发器]对COLUMNS_UPDATED()值的解析

对COLUMNS_UPDATED()值的解析 是一个仅可在 Insert or Update trigger 中调用的方法.该方法返回 一个 varbinary 的值, 存储了当次Insert 或是 ...
  • likecheng
  • likecheng
  • 2017年10月17日 18:17
  • 20

springmvc 可选的方法返回值解析

springmvc 通过@RequestMapping映射路径到方法体执行业务,业务执行之后,会有返回值,springmvc 根据返回值类型不同,解析出不同的视图。任何视图的渲染,springmvc都...
  • yingxiake
  • yingxiake
  • 2016年04月11日 23:21
  • 1551

COLUMNS_UPDATED原理

深入理解SQL Server 2005 中的 COLUMNS_UPDATED函数 COLUMNS_UPDATED函数能够出现在INSERT或UPDATE触发器中AS关键字后的任何位置,用来指示表或...
  • pengxuan
  • pengxuan
  • 2015年09月23日 16:57
  • 397

AJAX样例及返回值json解析

一  Js中的AJAX 样例,返回值JSON var el, li, i;   el = document.getElementById('add');    var url =  '/Serv...
  • yangqingfan
  • yangqingfan
  • 2015年07月16日 15:41
  • 212

javascript 解析ajax返回的xml和json格式的数据

写个例子,以备后用 一、JavaScript 解析返回的xml格式的数据: 1、javascript版本的ajax发送请求 (1)、创建XMLHttpRequest对象,这个对象就是ajax请求...
  • QH_JAVA
  • QH_JAVA
  • 2015年08月09日 16:18
  • 6096
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:對COLUMNS_UPDATED()返回值的解析
举报原因:
原因补充:

(最多只允许输入30个字)