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?
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().