How I can track all changes that made to database and save them for review?

I am building an application that users can add, update data.

Is there any way to track changes to a database and save these tracks in file or in anoth开发者_开发百科er database for example.

Thanks in advance.

I've never tried them but there are built in tools and utilities:

SQL Server Change Tracking

In SQL Server, there's Query Profiler which can run a trace against a live database and log all the queries to another database or a file

http://msdn.microsoft.com/en-us/library/ms187929.aspx

You can use triggers on the tables you want to track, and with those triggers you can determine which fields have changed (or if a record has been inserted or deleted), and then write the appropriate entry to an audit table.

Here is one article i found after a real quick search... Adding simple trigger-based auditing to your SQL Server database (Googling on the keywords sql server trigger audit should bring you mucho satisfaction and knowledge).

We currently use Audit tables to do this. So for every "Table" there is a corresponding "Table_AT" and there are triggers for every CRUD operation. So, on any CRUD function, the new data is written to the audit table.

This link has examples http://web.archive.org/web/20071006100909/http://sqlserver2000.databases.aspfaq.com:80/how-do-i-audit-changes-to-sql-server-data.html