Need to Log Data changes in readable format

David92595 · February 22nd, 2012, 09:38 PM

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?

Any help would be greatly appreciated.

Thank you!
David92595

irProject · February 23rd, 2012, 02:24 AM

Hi there.
you can use triggers:

Code:

CREATE TRIGGER Logger1
     ON Table1
     AFTER INSERT, UPDATE 
AS 

INSERT INTO tblLog (tableName, date, fieldName, fieldValue)
VALUES ('Table1', GetDate(), 'field1', INSERTED.field1);

GO

where Table1 and tblLog are your own tables:
Table1's Columns: ...,Field1,...
tblLog's Columns: ...,tableName, date, fieldName, fieldValue, ...