Oracle中对COLUMNS_UPDATED()返回值解析

核心提示： 编写Update 触发器If exists(select * from sysobjectswhere id=object_id(N'[dbo].[Tri_Test_Upd]')and objectproperty(id,N'istrigger')=1)DR

编写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