What is the best implementation for DB Audit Trail?

Question

A DB Audit Trail captures the User Last Modified, Modified Date, and Created Date.

There are several possible implementations:

• SQL Server Triggers
• Add UserModified, ModifiedDate, CreatedDate columns to the database and include logic in Stored Procedures or Insert, Update statements accordingly.

It would be nice if you include implementation (or link to) in your answer.

Answer 1

Depending on what you're doing, you might want to move the audit out of the data layer into the data access layer. It give you more control.

I asked a similar question wrt NHibernate and SqlServer here.

Answer 2

I totally second @IainMH (and voted him up).

You want to have it in your DAL and ideally tied to some kind of aspect/interceptor/code injection mechanism.

Answer 3

+2 for implementation of when/how to audit in the DAL.

As for where the audit entries themselves should live, it depends on how it will be visible. I'd do a separate table if users can view a separate "audit trail report," but tag existing tables if you want to display last modified-type audits inline.

Answer 4

You might want to check out this article by Hibernate developer Christian Bauer.

Answer 5

Here is the implementation I use to audit tables: Pop Rivett's SQL Server FAQ No.5: Pop on the Audit Trail