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

Is ther开发者_高级运维e a way to see the history or any other information of insertions into a specific table of an SQL Server database?

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.

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.

If you are using SQL Server 2008, you can use the new Change Data Capture feature. This saves you from having to write triggers on all your tables.

For 2005 use triggers, for 2008 you can use the change data capture.

Aside from using a trigger, you could do something like add a column named "InsertedDate" and record the current date there. This would require you do your insertions through a stored procedure though.