none
SQL Server中,使用触发器捕获当前表变更详细:变更时间,变更用户,变更字段及变更前的字段值。 RRS feed

  • 问题

  • SQL Server 应用中,有时候需要对一些敏感的数据进行追踪,获取每次数据变化的详细信息,如:变更时间,变更用户,变更字段及变更前的字段值。我们知道,从SQL Server 2008 开始,引入了变更数据捕获(CDC)这个特性,用于捕获应用到 SQL Server 表中的插入、更新和删除活动,但是变更数据捕获无法获取执行变更操作的用户。在这里要介绍一下使用触发器捕获当前表字段的变更详细。值得注意的是,使用触发器会增加数据库资源开销,降低数据库性能,因此,建议数据库是SQL Server 2005或者更早版本,在资源充裕并且工作负荷不大的情况使用这种方式追踪数据更改情况。

    2011年5月11日 7: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'

     

     

     

     

     

    2011年5月11日 7:09
    版主