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'