触发器－－用来获取那写字段更新

在网上找到了一个类似的文章http://www.cnblogs.com/delphi/articles/121516.html来引用这文章.我在它的基础上加了更详细注解。　　如下：          

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

select * from t_test



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  --列id
 
DECLARE  
 @i    TINYINT,  
 @j    TINYINT,  
 @iSegment TINYINT,  --以8为除数得余数（有少个分段)
 @iVal   TINYINT,  --被更新过的字段的十六进制值
 @iLog2  TINYINT  
 
DECLARE  
 @sSQL  VARCHAR(8000)  --执行的sql
 
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  --如果列总数是8的倍数那就取整数，否则取整数再加1
       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  
 --8个字段为一个分区，当表的列数大于8，那么计算被更新过的字段的十六进制值方法不同
 IF @iColCnt < 9  --表的列数小于8
   SET @iVal= COLUMNS_UPDATED()  
 ELSE  --表的列数大于8
   SET @iVal= SubString(COLUMNS_UPDATED(), @i + 1, 1)  
  
 --等于零，则表示当前小节所对应的8个字段没有被更新（修改）过,中止本次循环，继续下一次循环
 IF @iVal = 0  
  BEGIN  
    SET @i = @i + 1  
    CONTINUE  
  END  
  
 WHILE @iVal > 0  
  BEGIN  
   SET @j = 0  
   SET @iLog2 = @iVal / 2  --被更新字段十六进制值除2得余数
  
   WHILE @iLog2 > 0  
    BEGIN  
     SET @j = @j + 1  
     SET @iLog2 = @iLog2 / 2  
    END --end @iLog2 > 0 
  
    -- 得到被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) --执行sql 
  
    -- 测试输出
    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 -- end  @iVal > 0
 SET @i = @i + 1  
   END -- end @i < @iSegment
 
DROP TABLE #Inserted  
DROP TABLE #Deleted  
DROP TABLE #Temp  
 
go  


select from Syscolumns as S object_id('t_test')
SELECT  s.name ,s.colid FROM syscolumns as s WHERE id = object_id('t_test')  and s.colid = 1

Update T_test Set f_datetime = getdate(), f_float = 0.0123, f_int= 1  where f_id = 2

这样还真行，很高兴又学到了一招，：）这样很有意思，也有挑战性。

基礎知識:
COLUMNS_UPDATED()是一個僅可在 Insert or Update trigger 中調用的方法.
該方法返回 一個 varbinary 的值, 存儲了當次Insert 或是 Update 觸發器所對應的記錄在哪些字段上發生了Inserted or updated.在SQLSERVER 的聯機幫助[CREATE TRIGGER]和[IF UPDATE] 中 有對 COLUMNS_UPDATED () 方法的簡要描述.