Wednesday, March 9, 2011

SQL detect table data change with Update Trigger

ALTER TRIGGER [dbo].[Table_OnUpdate] ON [dbo].[Table]
FOR UPDATE
AS

DECLARE @firstname_changed BIT
DECLARE @lastname_changed BIT
DECLARE @middlename_changed BIT

SELECT
@firstname_changed = CASE WHEN i.firstname <> d.firstname THEN 1 ELSE 0 END,
@lastname_changed = CASE WHEN i.lastname <> d.lastname THEN 1 ELSE 0 END,
@middlename_changed = CASE WHEN i.middlename <> d.middlename THEN 1 ELSE 0 END
FROM inserted i INNER JOIN deleted d ON i.tableid=d.tableid

IF ((@firstname_changed = 1) OR (@lastname_changed = 1))
BEGIN
--Table data changed
END

No comments: