Best way to implement an audit trail in SQL Server?

Question

I don't know if these requirements are standard or not but I'm wondering is there a solution out there which can do the following:

• For a specified set of tables keep a copy of a record prior to changing it in a audit version of the relevant table.

I rather not have to code this for every table. I'm wondering if there is a solution you can install on top of SQL Server which will do this for you?

Answer 1

There are many ways to do that; it depends which version of SQL Server you are using.

Here are few

• Audit trail with shadow table and trigger Here is the link

• Also you can consider to use SQL Server 2008 Audit feature Here is the link