How to track Data Changes - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

How to track Data Changes

Author

Topic

Peace2007
Posting Yak Master

239 Posts

Posted - 2009-04-22 : 03:48:41

Hi,

I need to have a history of data changes in MS SQL2005 to know which data are updated by which queries at what time.

Any suggestion is appreciated

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-22 : 03:54:20

You can use triggers, you can use Change Data Capture if you on Enterprise Edition.
Soon WhiteFang will post something about "Do not use triggers because they are bad", but without anything substantial backing up his arguments. You can listen to him of you want.

A simple trigger like this will suffice

CREATE TRIGGER dbo.trgMyTrigger
ON MyTable
AFTER Update
AS

SET NOCOUNT ON

INSERT MyLog
SELECT d.Col1 AS OldValue, i.Col1 AS NewValue, GETDATE()
FROM inserted AS i
INNER JOIN deleted AS d ON d.pkCol = i.pkCol
WHERE d.Col1 <> i.Col1

E 12°55'05.63"
N 56°04'39.26"