CODEWITHSUNDEEP
sql-server
Bullish
Bullish

Reputation: 1

Recording all Sql Server Inserts and Updates

How can I record all the Inserts and Updates being performed on a database (MS SQL Server 2005 and above)?

Basically I want a table in which I can record all the inserts andupdates issues on my database. Triggers will be tough to manage because there are 100s of tables and growing.

Thanks Bullish

Upvotes: 0

Views: 202

Answers (2)

HLGEM
HLGEM

Reputation: 96640

We have hundreds of tables and growing and use triggers. In newer versions of SQL server you can use change Data Capture or Change Tracking but we have not found them adequate for auditing.

What we have is are two separate audit tables for each table (one for recording the details of the instance (1 row even if you updated a million records) and one for recording the actual old and new values), but each has the same structure and is created by running a dynamic SQL proc that looks for unauditied tables and creates the audit triggers. This proc is run every time we deploy.

Then you should also take the time to write a proc to pull the data back out of the audit tables if you want to restore the old values. This can be tricky to write on the fly with this structure, so it is best to have it handy before you have the CEO peering down your neck while you restore the 50,000 users accidentally deleted.

Upvotes: 2

Tahbaza
Tahbaza

Reputation: 9546

As of SQL Server 2008 and above you have change data capture.

Triggers, although unwieldy and a maintenance nightmare, will do the job on versions prior to 2008.

Upvotes: 1

Related Questions