Friday, June 25, 2010

Quick way to see what SQL data was updated

I had a crazy update query I was trying to troubleshoot, and for the life of me could not figure out why it insisted on updating some rows after they had already been updated by a prior run. Here's the quick one-time-use trigger I came up with to see exactly what was updated:


CREATE TRIGGER dbo.ViewUpdatedRecords
ON (table name)
AFTER UPDATE
AS
BEGIN
SELECT *
INTO UpdatedRecords
FROM INSERTED
END
GO


It only works one time, as SELECT INTO creates a table and will complain if a table by that name already exists. But it only took a minute to write, and helped me solve my issue very quickly.