Scripts to identify changed rows in a table | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Scripts to identify changed rows in a table

Does anyone have a script that identifies the rows in a table that have changed from the last time the script was run? I.E
If you first run the script at 12:00AM on 25-12-2005, it will identify rows in table_A. If you run it at 12:01AM and there have been some changes, it identifies the rows that have changed in the table.
Please help! If you arent part of the solution, then there is good money to be made prolonging the problem
Only possible if the table has a datetime column that is updated by a trigger that fires for the insert and update events. Like this: CREATE TRIGGER table_A_InsertUpdateTrigger
ON table_A
UPDATE table_A SET LastUpdate = GETDATE()
FROM table_A INNER JOIN inserted on table_A.key = inserted.key
END Now you can search on the LastUpdate column — the only thing is that you need to know when the search was last run. Other than that the only problem is that you can only see the last update on each row, so if you must be able to see ALL updates on each row you need to have a history table that you can fill using this same type of trigger.
Do you want to identify if a row has changed or do you want to identify the change(s)?
For the former you can use a rowversion column, for the latter Adriaan posted a solution. —
Frank Kalis
Microsoft SQL Server MVP
Ich unterstütze PASS Deutschland e.V.