-- 创建一个t表，来模拟要被监控的数据表

CREATE TABLE t(ID INT IDENTITY(1,1) NOT NULL, aid INT, aname NVARCHAR(20));

GO

INSERT INTO t VALUES(101,'AA');

GO

--创建另一张表Update_info，用来记录数据变更的详细信息

CREATE TABLE Update_info(Update_id uniqueidentifier,ID INT,aid_update NVARCHAR(128),aid_value int,aname_update NVARCHAR(128), aname_value NVARCHAR(20),Logged_USER NVARCHAR(128),UPDATE_USER NVARCHAR(128),UPDATE_TIME DATETIME);

GO

--在表格t上创建触发器，当对此表进行更新时，记录信息，并插入到表格Update_info

CREATE TRIGGER mytr ON t FOR UPDATE AS

DECLARE @Update_id AS uniqueidentifier

SET @Update_id= NEWID()

INSERT INTO Update_info(Update_id,ID,aid_update,aid_value,aname_update,aname_value,Logged_USER,UPDATE_USER,UPDATE_TIME)

SELECT @Update_id AS Update_id, A.ID,

(CASE WHEN ISNULL(B.aid,0)=ISNULL(A.aid,0) THEN NULL ELSE 'aid' END) AS aid_update,

(CASE WHEN ISNULL(B.aid,0)=ISNULL(A.aid,0) THEN NULL ELSE A.aid END) aid_value,

(CASE WHEN ISNULL(B.aname,'')=ISNULL(A.aname,'') THEN NULL ELSE 'aname' END) AS aid_update,

(CASE WHEN ISNULL(B.aname,'')=ISNULL(A.aname,'') THEN NULL ELSE A.aname END) aid_value,

ORIGINAL_LOGIN() AS Logged_USER, CURRENT_USER AS UPDATE_USER, GETDATE() AS UPDATE_TIME

FROM deleted A INNER JOIN inserted B on A.ID=B.ID

SELECT * FROM Update_info;

GO

-- 测试

UPDATE t SET aid=102,aname='BB'

摘自 徐悦 Tech Blog