Any Good Audit Trail Examples?

[Dave Babbitt]

Hi Guys!

What's the best way to keep an audit trail of every insert, update, and delete of a certain table? Any example code out there? I'm thinking in terms of a trigger for each event, for instance, the update trigger would insert a new record into the audit table with a field for each column in the deleted table and a field for each column in the inserted table.

Thanx

Dave

[Steve A]

Dave,

Will you want to audit every field or just particular fields in a table?


------------
Dave Babbitt at 6/17/2002 4:10:57 PM

Hi Guys!

What's the best way to keep an audit trail of every insert, update, and delete of a certain table? Any example code out there? I'm thinking in terms of a trigger for each event, for instance, the update trigger would insert a new record into the audit table with a field for each column in the deleted table and a field for each column in the inserted table.

Thanx

Dave

[sbawa]

Use trigger and copy info to an audit table; ie TableName_Audit.

Also, look at Lumigent log explorer.

shaleen

[wileyjack]

What I usually do in my environment is create a History table that is exactly the same as the table I want to keep 'audit' data for but add a 'ModifiedDate' Column. I then setup Update and Delete triggers (code below) to put any modified or deleted records into the History Table.

On the non-history table if I want to 'audit' inserts I usually just have a RecordDate Column and include the GETDATE() function in my insert. I see no need in duplicating the inserted data in two seperate tables, especially if the tables are both in the same db and on the same filegroup. But that's just my opinion.

For reporting I can then run a query based on date to tell me what records where entered when, when they were changed or when the were 'deleted'. I also now have row level recovery if one record was deleted or updated and the original needs to be restored.

CREATE TRIGGER [MainTable_DELETE_Trigger] ON MainTable
FOR DELETE
AS
INSERT MainTable_History
SELECT *,GETDATE()
FROM deleted

CREATE TRIGGER [MainTable_Update_Trigger] ON MainTable FOR UPDATE
AS
INSERT MainTable_History
SELECT *,GETDATE()
FROM deleted

Good Luck!

WiLeYjAcK