Need to log data changes in readable format

[David92595]

Hello,

I need to find a way to log data changes that are being made in our database. From the reading I've done online I need to turn on the system stored procedure for change data capture (sys.sp_cdc_enable_db), but I only have the standard edition of SQL Server 2008 R2. Which I've found requires the Enterprise or Developer editions to run.

Does anyone have a work around or an alterative for this?

Anything is better then what I have now.

Thank you!
David92595

 

[SQL_Hell]

I don't think there is an out of the box solution, one way is to program your own auditing using triggers
Example here:

http://sqlserver2000.databases.aspfaq.com/how-do-i-audit-changes-to-sql-server-data.html

There are also some third party product suh as

http://www.apexsql.com/default.aspx

 

[tehNellie]

triggers on update/delete and an "audit" table would do it.

Effectively mirror the live table, use the PK from the live as an FK on the audit table, whenever an UPDATE is received on the live table, insert the current data, into the audit table before the UPDATE is applied. The live table needs an "Updated" datefield and a "Changedby" which captures the username (Suser_sname()) making the change.

It's diskspace intensive, but it's clear and simple to implement. Your live table only ever contains the current live record but you have a history of changes made to that record since its creation that can be queried when necessary.

It also places a performance overhead as every update/delete is effectively having to run an additional insert query to create the audit record

 

[bparkinson]

triggers on update/delete and an "audit" table would do it.

Effectively mirror the live table, use the PK from the live as an FK on the audit table, whenever an UPDATE is received on the live table, insert the current data, into the audit table before the UPDATE is applied. The live table needs an "Updated" datefield and a "Changedby" which captures the username (Suser_sname()) making the change.

It's diskspace intensive, but it's clear and simple to implement. Your live table only ever contains the current live record but you have a history of changes made to that record since its creation that can be queried when necessary.

It also places a performance overhead as every update/delete is effectively having to run an additional insert query to create the audit record

tehNellie has this exactly right. I've done this, it is acomplishable if you have good hardware.