There are different reasons for having an audit trail on your data. Some companies have to because of legal obligations, such as Sarbanes-Oxley or FDA regulations. But I also find it very useful for debugging purposes. It shows you what was in your database at any point in time.

So how to implement an audit trail? Different visions exist. The one I prefer is to use a shadow table for each table that exists in the database. Every time a record is inserted, updated or deleted in a table, the record is also inserted in the corresponding shadow table. For inserting the record also in the shadow table, I use triggers on the original table that will fire whenever something happens. Other names you may encounter on the web for shadow tables are archive table or audit table.

The big advantage of this method is that if originally you did not foresee audit trailing, you can add it afterwards. The only thing you need to do is add the triggers on the base tables and create the shadow table. No changes have to be made to stored procedures or applications working with your database.

To make the implementation of the audit trail easier, I wrote a script to automate the creation of shadow tables and triggers. You can download the code here. If you would like some more background, please see my article on the subject on Code Project.