Create Data Audit in SQL Server

Question

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.

Lets say I have the following table:

[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))

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.

Therefore is there a better way of doing this?

Depending on your situation, [this answer](http://stackoverflow.com/questions/6998774/update-function-in-transact-sql-a-boolean-condition/7151476#7151476) might be of some help. (It's a simplified version of what @HLGEM is talking about.) — Andriy M, Aug 22 '11 at 23:54

score 3 · Accepted Answer · answered Aug 22 '11 at 16:30

3

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)

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

answered Aug 22 '11 at 16:30

HardCode

6,497
4
31
54

I use ASP.NET Forms Authentication / Membership. Is there anyway of getting the UserID in the Trigger? Currently I pass a @UserID parameter from the app – Curtis Aug 23 '11 at 09:05
1

Hey do you have any suggestions for how I can do this with Forms Authentication / Membership? – Curtis Sep 14 '11 at 10:08
I'm not sure about Membership - I've never used it - but for non-Active Directory solutions, you'll have to pass the user ID to the stored procedure as a regular old parameter. – HardCode Mar 29 '12 at 18:18
To use forms authentication you can put the UserID for your application into the connection string. For example my application is called SGA. In the connection string included ";Application Name=SGA User MyUserName". Then you can use T-SQL's APP_NAME() function and substring the user name out of it. – Jeff Stock Feb 13 '14 at 15:02

score 1 · Answer 2 · answered Aug 22 '11 at 21:14

We use triggers (the only way to go and make sure you write them to handle mulitple record inserts/updates/deletes) and our structure is a bit differnt. First we have a table that stores the information about the action, the person/aplicationthat did it the date thenumber of affected records. Then we havea table that stores the details. This table has an identifier column, column_name, old value, new value. (we use nvarchar (max) for the columns in the audit table) This way if the table gets new columns we don't have to worry about changing the audit tables. We have one set of audit tables for each table we audit.

Newer versions of Sql server have change tracking but we don't find it has enough detail for the auditing we need and it deletes the data too quickly unless you move it to another permanent table.

score 0 · Answer 3 · answered Aug 22 '11 at 17:22

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.

Therefore is there a better way of doing this?

Not really. You can make the implementation better via trigger's as HardCode mentions but you still have to modify the audit and related scirpts.

I've witnessed attempts to make this "better" where you don't have to update a trigger or audit table. This always results in trading the minor problem (hey a column got added and I've got to do some stuff) for much larger ones. Usually performance, correctness and reliability issues.

Create Data Audit in SQL Server

3 Answers3

Linked