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

作者:徐悦 Tech Blog  发布日期:2011-10-24 18:41:09
-- 创建一个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'  

延伸阅读:

Tag标签: 触发器监控  
  • 专题推荐

About IT165 - 广告服务 - 隐私声明 - 版权申明 - 免责条款 - 网站地图 - 网友投稿 - 联系方式
本站内容来自于互联网,仅供用于网络技术学习,学习中请遵循相关法律法规