Oracle中对COLUMNS_UPDATED()返回值解析

     来源：赛迪网    作者：3946469

本文详细讲述了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(1, 1) Primary Key,
f_charChar(8) default '',
f_varchar　 varchar(8) default '',
f_nvarchar　 nvarchar(8) default '',
f_datetime　 datetime default getdate(),
f_intint default 0,
f_bigint　 bigint default 0,
f_decimal　 decimal(18, 6) default 0.00,
f_number　 numeric(18, 6) default 0.00,
f_floatfloat 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
　@iTINYINT,
　@jTINYINT,
　@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分解, 就可算得被修改列的列表。