在網上找到了一個類似的文章http://www.cnblogs.com/delphi/articles/121516.html來引用這文章.我在它的基礎上加了更詳細注解。 如下：
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
select * from t_test
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 --列id
DECLARE
@i TINYINT,
@j TINYINT,
@iSegment TINYINT, --以8為除數得余數（有少個分段)
@iVal TINYINT, --被更新過的字段的十六進制值
@iLog2 TINYINT
DECLARE
@sSQL VARCHAR(8000) --執行的sql
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 --如果列總數是8的倍數那就取整數，否則取整數再加1
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
--8個字段為一個分區，當表的列數大于8，那么計算被更新過的字段的十六進制值方法不同
IF @iColCnt < 9 --表的列數小于8
SET @iVal= COLUMNS_UPDATED()
ELSE --表的列數大于8
SET @iVal= SubString(COLUMNS_UPDATED(), @i + 1, 1)
--等于零，則表示當前小節所對應的8個字段沒有被更新（修改）過,中止本次循環，繼續下一次循環
IF @iVal = 0
BEGIN
SET @i = @i + 1
CONTINUE
END
WHILE @iVal > 0
BEGIN
SET @j = 0
SET @iLog2 = @iVal / 2 --被更新字段十六進制值除2得余數
WHILE @iLog2 > 0
BEGIN
SET @j = @j + 1
SET @iLog2 = @iLog2 / 2
END --end @iLog2 > 0
-- 得到被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) --執行sql
-- 測試輸出
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 -- end @iVal > 0
SET @i = @i + 1
END -- end @i < @iSegment
DROP TABLE #Inserted
DROP TABLE #Deleted
DROP TABLE #Temp
go
select from Syscolumns as S object_id('t_test')
SELECT s.name ,s.colid FROM syscolumns as s WHERE id = object_id('t_test') and s.colid = 1
Update T_test Set f_datetime = getdate(), f_float = 0.0123, f_int= 1 where f_id = 2
這樣還真行，很高興又學到了一招，：）這樣很有意思，也有挑戰性。
基礎知識:
COLUMNS_UPDATED()是一個僅可在 Insert or Update trigger 中調用的方法.
該方法返回 一個 varbinary 的值, 存儲了當次Insert 或是 Update 觸發器所對應的記錄在哪些字段上發生了Inserted or updated.在SQLSERVER 的聯機幫助[CREATE TRIGGER]和[IF UPDATE] 中 有對 COLUMNS_UPDATED () 方法的簡要描述.
posted on 2005-09-06 09:30
Harryson 閱讀(1178)
評論(0) 編輯 收藏 所屬分類:
SqlServer