SQL Server 使用触发器监控数据变更

-- 创建一个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