-- 等于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
Oracle中对COLUMNS_UPDATED()返回值解析
本文具体讲述了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
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
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);
@iVal = 2^(1-1) + 2^(2-1) = 3;
@iVal = 2^(2-1) + 2^(5-1) + 2^(8-1) = 146;
...
@iVal = 2^(1-1) + 2^(2-1) + ... + 2^(8-1) = 255;
-- 等于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
