对COLUMNS_UPDATED()返回值的解析

<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

<script type="text/javascript"> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

需求来源:
客户要求[某些特定的表]能[自定义预警报告].

(在特定的表上)用户可定义某些字段有修改时,向有关用户发出消息警报<内容大致是 xx单据的xx单号的xx字段由old变为了new>.最终目的是由消息控制模块向消息接收人报告这一变更.

基础知识:
COLUMNS_ UPDATED()是一个仅可在InsertorUpdatetrigger中调用的方法.
该方法返回一个varbinary的值,存储了当次Insert或是Update触发器所对应的记录在哪些字段上发生了Inserted or UPDATED.在SQLSERVER 的联机帮助[CREATETRIGGER]和[IFUPDATE]中有对 COLUMNS_ UPDATED()方法的简要描述.

公司要求用Trigger实现:(为每个[特定的表]编写一个特定的UPDATE触发器.)
主要难点是穷举IFUPDATE(column)的方法不可行.其他业务实现无问题.
后来仔细琢磨 COLUMNS_ UPDATED()所返回的值,问题得到解决.

这裡只是讲述对 COLUMNS_ UPDATED()所返回的值的 解析和运用.就不考虑用户指定变更字段及插入记录到消息表的那部分实现过程了.

--测试数据準备.

Ifexists(select*fromsysobjectswhereid=object_id(N'[dbo].[T_Test]')andxtype='u')
  DROPTableT_Test
go

CREATE Table T_Test(
f_id    int IDENTITY(1,1)PrimaryKey,
f_char    Char(8)default'',
f_varchar   varchar(8)default'',
f_nvarchar   nvarchar(8)default'',
f_datetime   datetimedefaultgetdate(),
f_int    intdefault0,
f_bigint   bigintdefault0,
f_decimal   decimal(18,6)default0.00,
f_number   numeric(18,6)default0.00,
f_float    floatdefault0.00
)
go

INSERTINTOT_Test(f_char)values('001')
INSERTINTOT_Test(f_char)values('002')
go


--编写Update触发器

If exists(select*fromsysobjectswhereid=object_id(N'[dbo].[Tri_Test_Upd]')andobjectproperty(id,N'istrigger')=1)
   DROPTRIGGERTri_Test_Upd
go

CREATE TRIGGERTri_Test_UpdONT_Test --WITHENCRYPTION
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)FROMsys COLUMNS WHEREid=object_id(@sTable)

-- 以8个字段为一小段
SET @iSegment=CASE
       WHEN@iColCnt/8=@iColCnt/8.0
        THEN
         @iColCnt/8
        ELSE
         @iColCnt/8+1
        END 1 <script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

<script type="text/javascript"> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>