How can I view the history of inserts to a table?

Question

Is there a way to see the history or any other information of insertions into a specific table of an SQL Server database?

Answer 1

Unless you are recording this information somewhere using a trigger, you would need some way of looking at the information in the transaction log. There are commercial tools like Lumigent for this.

Answer 2

You could use a trigger

Create a trigger on the table watching for inserts, updates, and deletes). The trigger would insert into another table (a history table).

This adds extra overhead, though, so I wouldn't do this on a really heavily updated table.

Look at this page for an example of how this is done.

This page has some code that generates the audit trail code for you.

Here is another SOF question about doing this using triggers.