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'