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

在网上找到了一个类似的文章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 () 方法的簡要描述.