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.EIf 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
FOR INSERT, UPDATE AS IF @@ROWCOUNT > 0
BEGIN
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
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
]]>