本文詳細講述了Oracle中對COLUMNS_UPDATED()返回值解析
需求來源
客戶要求[某些特定的表]能[自定義預警報告]
(在特定的表上)用戶可定義某些字段有修改時向有關用戶發出消息警報<內容大致是 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_idint IDENTITY( ) Primary Key
f_charChar() default
f_varchar varchar() default
f_nvarchar nvarchar() default
f_datetime datetime default getdate()
f_intint default
f_bigint bigint default
f_decimal decimal( ) default
f_number numeric( ) default
f_floatfloat default
)
go
INSERT INTO T_Test (f_char) values()
INSERT INTO T_Test (f_char) values()
go
編寫Update 觸發器
If exists(select * from sysobjects
where id=object_id(N[dbo][Tri_Test_Upd])
and objectproperty(idNistrigger)=)
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 <
RETURN
DECLARE
@sTable VARCHAR()
@sPKName VARCHAR()
@sColName VARCHAR()
DECLARE
@iColCnt INT
@iColId INT
DECLARE
@iTINYINT
@jTINYINT
@iSegment TINYINT
@iVal TINYINT
@iLog TINYINT
DECLARE
@sSQL VARCHAR()
SET @sTable = t_test
SET @sPKName = f_id
求得當前表列個數
SELECT @iColCnt = Count() FROM syscolumns WHERE id = object_id(@sTable)
以 個字段為一小段
SET @iSegment = CASE
WHEN @iColCnt / = @iColCnt /
THEN
@iColCnt /
ELSE
@iColCnt / +
END
將數據存入 臨時表
SELECT * INTO #Inserted FROM Inserted
SELECT * INTO #Deleted FROM Deleted
中間處理數據用
CREATE TABLE #Temp(
f_PKVal varchar() not null primary key
f_OldVal varchar()
f_NewVal varchar()
)
SET @i =
WHILE @i < @iSegment
BEGIN
IF @iColCnt <
SET @iVal= COLUMNS_UPDATED()
ELSE
SET @iVal= SubString(COLUMNS_UPDATED() @i + )
等於 則表示當前小節所對應的個字段無一被改
IF @iVal =
BEGIN
SET @i = @i +
CONTINUE
END
WHILE @iVal >
BEGIN
SET @j =
SET @iLog = @iVal /
WHILE @iLog >
BEGIN
SET @j = @j +
SET @iLog = @iLog /
END
得到被Update 的 列ID
SET @iColId = * @i + @j +
將Update列名 賦予 @sColName
SELECT @sColName = Sname
FROM Inserted as I
Deleted as D
Syscolumns as S
WHERE IF_id = DF_id
AND Sid = object_id(@sTable)
AND lid = @iColId
Truncate table #Temp
拼成動態語句
SET @sSQL = INSERT INTO #Temp (f_PkVal f_OldVal f_NewVal) +
SELECT Convert( varchar() I + @sPkName + ) +
Convert( varchar() D + @sColName + ) +
Convert( varchar() 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( @j)
END
SET @i = @i +
END
DROP TABLE #Inserted
DROP TABLE #Deleted
DROP TABLE #Temp
go
測試數據
Update T_test Set f_datetime = getdate()
f_float = f_int=
上面Update 語句共修改了三個列
實際輸出
)
f_int
f_int
)
f_datetime May :PM May :PM
f_datetime May :PM May :PM
)
f_float
f_float
算法
COLUMNS_UPDATED()方法返回的 varbinary是以每個小節存儲個字段(的修改狀態)的方式記錄了當前觸發器所有列的修改情形因此程序以個字段為一片段來循環處理所有字段
SET @iVal= SubString(COLUMNS_UPDATED() @i + )程序用上面語句將一小節轉化為整型測試發現:（當且謹當這一小片只有一個字段有修改時）
@iVal = = ^();
@iVal = = ^();
@iVal = = ^();
@iVal = = ^();
@iVal = = ^();
@iVal = = ^();
@iVal = = ^();
@iVal = = ^();
而當且謹當個字段有修改時:
@iVal = ^() + ^() = ;
而第 三個字段有修改時:
@iVal = ^() + ^() + ^() = ;
當個字段都有修改時:
@iVal = ^() + ^() + + ^() = ;
也就是說 無論怎樣修改@iVal的值不外乎是^n (n> and n < int)這一數組型成的[和組合](組合時每個數組成員最多出現一次)因此反過來推算: 對 @iVal 按 ^n分解 就可算得被修改列的列表
From:http://tw.wingwit.com/Article/program/Oracle/201311/17153.html
