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

博主引用网上文章并添加详细注解。介绍了COLUMNS_UPDATED()方法,它仅可在Insert or Update trigger中调用,返回varbinary值,存储对应记录在哪些字段发生Inserted or updated,SQLSERVER联机帮助中有简要描述。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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

None.gif CREATE   Table  T_Test (
None.giff_id    
int   IDENTITY ( 1 1 Primary   Key ,
None.giff_char    
Char ( 8 default   '' ,
None.giff_varchar   
varchar ( 8 default   '' ,
None.giff_nvarchar   
nvarchar ( 8 default   '' ,
None.giff_datetime   
datetime   default   getdate (),
None.giff_int    
int   default   0 ,
None.giff_bigint   
bigint   default   0 ,
None.giff_decimal   
decimal ( 18 6 default   0.00 ,
None.giff_number   numeric(
18 6 default   0.00 ,
None.giff_float    
float   default   0.00
None.gif)
None.gif
go
None.gif
None.gif
INSERT   INTO  T_Test (f_char)  values ( ' 001 ' )
None.gif
INSERT   INTO  T_Test (f_char)  values ( ' 002 ' )
None.gif
go
None.gif
None.gif
select   *   from  t_test
None.gif
None.gif
None.gif
None.gif
If   exists ( select   *   from  sysobjects  where  id = object_id (N ' [dbo].[Tri_Test_Upd] ' and   objectproperty (id,N ' istrigger ' ) = 1 )  
None.gif   
DROP   TRIGGER  Tri_Test_Upd  
None.gif
go   
None.gif 
None.gif
CREATE   TRIGGER  Tri_Test_Upd  ON  T_Test  -- WITH ENCRYPTION  
None.gif
FOR   UPDATE   
None.gif
AS   
None.gif
None.gif
None.gif
DECLARE   @iRowCnt   INT    -- 列数
None.gif
 
None.gif
SET   @iRowCnt   =   @@rowcount   
None.gif 
None.gif
IF   @iRowCnt   <   1   
None.gif
RETURN   
None.gif 
None.gif
DECLARE   
None.gif 
@sTable    VARCHAR ( 128 ),   -- 表名
None.gif
  @sPKName   VARCHAR ( 32 ),   -- 主鍵名
None.gif
  @sColName   VARCHAR ( 128 )   -- 列名
None.gif
 
None.gif
DECLARE   
None.gif 
@iColCnt    INT ,   -- 列数
None.gif
  @iColId    INT    -- 列id
None.gif
 
None.gif
DECLARE   
None.gif 
@i      TINYINT ,  
None.gif 
@j      TINYINT ,  
None.gif 
@iSegment   TINYINT ,   -- 以8为除数得余数(有少个分段)
None.gif
  @iVal     TINYINT ,   -- 被更新过的字段的十六进制值
None.gif
  @iLog2    TINYINT   
None.gif 
None.gif
DECLARE   
None.gif 
@sSQL    VARCHAR ( 8000 )   -- 执行的sql
None.gif
 
None.gif
SET   @sTable   =   ' t_test '   
None.gif
SET   @sPKName   =   ' f_id '   
None.gif 
None.gif
--  求得当前表列个数  
None.gif
SELECT   @iColCnt   =   Count ( 1 FROM  syscolumns  WHERE  id  =   object_id ( @sTable )  
None.gif 
None.gif
--  以8个字段为一小段  
None.gif
SET   @iSegment   =   CASE   
None.gif      
WHEN   @iColCnt   /   8   =   @iColCnt   /   8.0    -- 如果列总数是8的倍数那就取整数,否则取整数再加1
None.gif
        THEN   
None.gif        
@iColCnt   /   8   
None.gif       
ELSE   
None.gif        
@iColCnt   /   8   +   1   
None.gif       
END   
None.gif
--  将数据存入临时表
None.gif
SELECT   *   INTO  #Inserted  FROM  Inserted   -- 更新的数据
None.gif
SELECT   *   INTO  #Deleted  FROM  Deleted   -- 更新前的数据
None.gif
 
None.gif
-- 中间处理数据用
None.gif
CREATE   TABLE  # Temp (  
None.gif  f_PKVal  
varchar ( 254 not   null   primary   key ,   -- 主键
None.gif
  f_OldVal   varchar ( 254 ),   -- 原来的值
None.gif
  f_NewVal   varchar ( 254 )   -- 新的值
None.gif
)  
None.gif 
None.gif
SET   @i   =   0    -- 循环整个分段(也就所有的字段)
None.gif
 
None.gif
WHILE   @i   <   @iSegment   
None.gif  
BEGIN   
None.gif 
-- 8个字段为一个分区,当表的列数大于8,那么计算被更新过的字段的十六进制值方法不同
None.gif
  IF   @iColCnt   <   9    -- 表的列数小于8
None.gif
    SET   @iVal =  COLUMNS_UPDATED()  
None.gif 
ELSE    -- 表的列数大于8
None.gif
    SET   @iVal =   SubString (COLUMNS_UPDATED(),  @i   +   1 1 )  
None.gif  
None.gif 
-- 等于零,则表示当前小节所对应的8个字段没有被更新(修改)过,中止本次循环,继续下一次循环
None.gif
  IF   @iVal   =   0   
None.gif  
BEGIN   
None.gif    
SET   @i   =   @i   +   1   
None.gif    
CONTINUE   
None.gif  
END   
None.gif  
None.gif 
WHILE   @iVal   >   0   
None.gif  
BEGIN   
None.gif   
SET   @j   =   0   
None.gif   
SET   @iLog2   =   @iVal   /   2    -- 被更新字段十六进制值除2得余数
None.gif
  
None.gif   
WHILE   @iLog2   >   0   
None.gif    
BEGIN   
None.gif     
SET   @j   =   @j   +   1   
None.gif     
SET   @iLog2   =   @iLog2   /   2   
None.gif    
END   -- end @iLog2 > 0 
None.gif
  
None.gif    
--  得到被Update 的 列ID  
None.gif
     SET   @iColId   =   8   *   @i   +   @j   +   1   
None.gif  
None.gif    
--  将Update列名 赋予 @sColName  
None.gif
     SELECT   @sColName   =  S.name  FROM  Inserted  as  I,Deleted  as  D,Syscolumns  as  S  WHERE  I.F_id  =  D.F_id  
None.gif    
AND  S.id  =   object_id ( @sTable )  
None.gif    
AND  S.colid  =   @iColId   
None.gif  
None.gif    
Truncate   table  # Temp   
None.gif    
-- 拼成功能语句
None.gif
     SET   @sSQL   =   ' INSERT INTO #Temp (f_PkVal, f_OldVal, f_NewVal)  '   +   
None.gif       
' SELECT Convert( varchar(200), I. '   +   @sPkName   +   ' ),  '   +   
None.gif       
' Convert( varchar(200), D. '   +   @sColName   +   ' ),  '   +   
None.gif       
' Convert( varchar(200), I. '   +   @sColName   +   ' '   +   
None.gif       
' FROM  #Inserted as I, #Deleted as D  '   +   
None.gif       
' WHERE I. '   +   @sPKName   +   '  = D. '   +   @sPKName   +   
None.gif       
'  AND I. '   +   @sColName   +   '  <> D. '   +   @sColName   
None.gif  
None.gif    
EXEC ( @sSQL -- 执行sql 
None.gif
  
None.gif    
--  测试输出
None.gif
     Select  f_pkVal,   @sColName   as  f_column_name, f_oldVal, f_newVal   FROM  # temp   
None.gif    
--  实际上用将信息处理后插入消息表
ExpandedBlockStart.gifContractedBlock.gif
     /**/ /**/ /**/ /*  
InBlock.gif    ..  
InBlock.gif     
InBlock.gif    INSERT INTO T_Message(.)  
InBlock.gif    SELECT 要组成的内容
InBlock.gif    FROM #temp  
ExpandedBlockEnd.gif    
*/
  
None.gif  
None.gif    
SET   @iVal   =   @iVal   -   Power ( 2 @j )  
None.gif  
END   --  end  @iVal > 0
None.gif
  SET   @i   =   @i   +   1   
None.gif   
END   --  end @i < @iSegment
None.gif
 
None.gif
DROP   TABLE  #Inserted  
None.gif
DROP   TABLE  #Deleted  
None.gif
DROP   TABLE  # Temp   
None.gif 
None.gif
go   
None.gif
None.gif
None.gif
select   from  Syscolumns  as  S  object_id ( ' t_test ' )
None.gif
SELECT   s.name ,s.colid  FROM  syscolumns  as  s  WHERE  id  =   object_id ( ' t_test ' )   and  s.colid  =   1
None.gif
None.gif
Update  T_test  Set  f_datetime  =   getdate (), f_float  =   0.0123 , f_int =   1    where  f_id  =   2
None.gif
None.gif


这样还真行,很高兴又学到了一招,:)这样很有意思,也有挑战性。

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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值