COLUMNS_UPDATED()返回值的解析_sqlserver

需求碓:
客粢[某些特定的表]能[自定xA警蟾].

(在特定的表上)用艨啥x某些字段有修改r,向有P用舭l出消息警<热荽笾率 xx xx 翁的xx字段由 old 榱 new>. 最K目的是由消息控制模K向消息接收人蟾孢@一更.

http://www.gaodaima.com/35270.htmlCOLUMNS_UPDATED()返回值的解析_sqlserver

基A知R:
COLUMNS_UPDATED()是一H可在 Insert or Update trigger 中{用的方法.
方法返回 一 varbinary 的值, 存α水次Insert 或是 Update |l器所挠在哪些字段上l生了Inserted or updated.在SQLSERVER 的C助[CREATE TRIGGER]和[IF UPDATE] 中 有 COLUMNS_UPDATED () 方法的要描述.

公司要求用Trigger F: (槊[特定的表]一特定的UPDATE |l器.)
主要yc是Fe IF UPDATE(column)的方法不可行.其他IFo}.
后碜屑琢磨COLUMNS_UPDATED() 所返回的值,}得到解Q.

@e只是v述COLUMNS_UPDATED()所返回的值的解析和/用. 就不考]用糁付ㄗ更字段及插入到消息表的那部分F^程了.

–y.

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 |l器

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
— 嫒 Rr表
SELECT * INTO #Inserted FROM Inserted
SELECT * INTO #Deleted FROM Deleted

— 中g
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, t表示前小所8字段o一被改.
 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列名 x予 @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
  — 拼成BZ句
  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)

  — y出
   Select f_pkVal,  @sColName as f_column_name, f_oldVal, f_newVal  FROM #temp
  — H上用 ⑿畔⑻砗蟛迦胂⒈
  /*
  …..
  
  INSERT INTO T_Message(….)
   SELECT 要M的热
    FROM #temp
  */

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

 SET @i = @i + 1
 END

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

go

—  y
Update T_test Set f_datetime = getdate(), f_float = 0.0123, f_int= 1

—  上面Update Z句共修改了三列
—  H出
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字段(的修改B)的方式了前|l器所有列的修改情形.因此程序以8字段橐黄硌h硭凶侄.
SET @iVal= SubString(COLUMNS_UPDATED(), @i + 1, 1)
程序用上面Z句⒁恍」D化檎. ylF:
且@一小片只有一字段有修改r
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字段有修改r:
@iVal = 2^(1-1) + 2^(2-1) = 3;
而第 2,5,8 三字段有修改r:
@iVal = 2^(2-1) + 2^(5-1) + 2^(8-1) = 146;
…
8字段都有修改r:
@iVal = 2^(1-1) + 2^(2-1) + … + 2^(8-1) = 255;

也就是f o怎有薷,@iVal的值,不外乎是2^n – 1(n>0 and n <9, int)@一到M型成的[和M合](M合r每到M成T最多出F一次).因此反^硗扑: @iVal 按 2^n分解, 就可算得被修改列的列表.

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

欢迎大家阅读《COLUMNS_UPDATED()返回值的解析_sqlserver》,跪求各位点评,若觉得好的话请收藏本文，by 搞代码

喜欢 (0)赏

[搞代码]

分享 (0)