在SQL Server中创建数据审核 [英] Create Data Audit in SQL Server

问题描述

我最近被赋予在数据库表上创建审核的任务，以便可以跟踪对任何列所做的任何更改.

I've recently been given the task of creating an Audit on a database table so that any changes made to any columns can be tracked.

让我们说一下下表:

[TableA]
------
ID
ColumnA
ColumnB
ColumnC

为了进行审核，我创建了一个表，例如:

For Auditing I've created a table such as:

[TableA.Audit]
------
ID
TableAID
UserID
Date (default value = getdate())
ColumnA
ColumnB
ColumnC

然后，我编写了一个脚本，例如:

I've then wrote a script like:

DECLARE @currentColumnA int
       ,@currentColumnB int
       ,@currentColumnC int

SELECT TOP 1 @currentColumnA=ColumnA
            ,@currentColumnB=ColumnB
            ,@currentColumnC=ColumnC
FROM [TableA]
WHERE ID=@TableAID

UPDATE [TableA]
SET ColumnA=@ColumnA
    ,ColumnB=@ColumnB
    ,ColumnC=@ColumnC
WHERE ID=@TableAID

INSERT INTO [TableA.Audit] (TableAID, UserID, ColumnA, ColumnB, ColumnC)
VALUES (@TableAID, @UserID, NULLIF(@ColumnA, @currentColumnA), NULLIF(@ColumnB, @currentColumnB), NULLIF(@ColumnC, @currentColumnC))

与此相关的问题是，如果要向TableA添加ColumnD字段，则必须编辑TableA.Audit表以及上面的脚本.

The problem with this, is that if I was to add a ColumnD field to TableA I'm going to have to edit my TableA.Audit table as well as the above script.

因此有更好的方法吗?

推荐答案

最好为表的AFTER INSERT，AFTER DELETE和AFTER UPDATE编写触发器.这样，在表中插入，更新或删除数据的任何时间(应用程序，Management Studio等)都会被记录.您必须为审核操作添加一个字段，然后在触发器中插入操作的文字(例如'I'或'INSERT').我以这种方式构造审计表:

You are better off writing triggers for the table for AFTER INSERT, AFTER DELETE, and AFTER UPDATE. This way, any time ANYTHING (application, Management Studio, etc.) that inserts, updates, or deletes data in the table will get logged. You'll have to add a field for the audit action, and in your trigger insert the literal for the action (e.g. 'I' or 'INSERT'). I structure my audit tables in this way:

audit_id: INT IDENTITY 
audit_date: DATETIME GETDATE() 
audit_action: VARCHAR(16) ... or you can use CHAR(1) 
audit_user: VARCHAR(128) SUSER_SNAME()
(the fields from the table being audited)

由于我们的应用程序使用Active Directory，因此我可以将audit_user默认为SUSER_SNAME().

Since our apps use Active Directory, I can default audit_user to SUSER_SNAME().

这篇关于在SQL Server中创建数据审核的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！