在网上找到了一个类似的文章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 () 方法的簡要描述.