分享
 
 
 

對COLUMNS_UPDATED()返回值的解析

王朝other·作者佚名  2006-01-09
窄屏简体版  字體: |||超大  

需求來源:

客戶要求[某些特定的表]能[自定義預警報告].

(在特定的表上)用戶可定義某些字段有修改時,向有關用戶發出消息警報<內容大致是 xx 單據的 xx 單號的xx字段由 old 變為了 new>. 最終目的是由消息控制模塊向消息接收人報告這一變更.

基礎知識:

COLUMNS_UPDATED()是一個僅可在 Insert or Update trigger 中調用的方法.

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

公司要求用Trigger 實現: (為每個[特定的表]編寫一個特定的UPDATE 觸發器.)

主要難點是窮舉 IF UPDATE(column)的方法不可行.其他業務實現無問題.

后來仔細琢磨COLUMNS_UPDATED() 所返回的值,問題得到解決.

這裡只是講述對COLUMNS_UPDATED()所返回的值的解析和運用. 就不考慮用戶指定變更字段及插入記錄到消息表的那部分實現過程了.

--測試數據準備.

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 觸發器

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

-- 將數據存入 臨時表

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

IF @iColCnt < 9

SET @iVal= COLUMNS_UPDATED()

ELSE

SET @iVal= SubString(COLUMNS_UPDATED(), @i + 1, 1)

-- 等於0, 則表示當前小節所對應的8個字段無一被改.

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列名 賦予 @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)

-- 測試輸出

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

SET @i = @i + 1

END

DROP TABLE #Inserted

DROP TABLE #Deleted

DROP TABLE #Temp

go

-- 測試數據

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

-- 上面Update 語句共修改了三個列

-- 實際輸出

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個字段(的修改狀態)的方式記錄了當前觸發器所有列的修改情形.因此程序以8個字段為一片段來循環處理所有字段.

SET @iVal= SubString(COLUMNS_UPDATED(), @i + 1, 1)

程序用上面語句將一小節轉化為整型. 測試發現:

當且謹當這一小片只有一個字段有修改時

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個字段有修改時:

@iVal = 2^(1-1) + 2^(2-1) = 3;

而第 2,5,8 三個字段有修改時:

@iVal = 2^(2-1) + 2^(5-1) + 2^(8-1) = 146;

...

當8個字段都有修改時:

@iVal = 2^(1-1) + 2^(2-1) + ... + 2^(8-1) = 255;

也就是說 無論怎樣修改,@iVal的值,不外乎是2^n - 1(n>0 and n <9, int)這一數組型成的[和組合](組合時每個數組成員最多出現一次).因此反過來推算: 對 @iVal 按 2^n分解, 就可算得被修改列的列表.

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

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有