If we receive an update statement that does not check if the value has changed in the where clause, what are the different ways to ignore that update inside a trigger?
I know we can do a comparison of each individual field (handling the ISNULL side as well), but where it's a table that has 50+ fields, is there a faster/easier way to do it?
Note: This could be used to reduce IO/noise in audit records etc...
For a table of:
CREATE TABLE myTest (ID int NOT NULL IDENTITY(1,1), Field1 varchar(10) NULL, Field2 varchar(20) NULL)
with an after updater trigger containing:
INSERT INTO myTestAudit (ID, Field1, Field2, DateTimeUpdate) SELECT ID, Field1, Field2, getDate() FROM inserted
with initial values:
INSERT INTO myTest (Field1, Field2) SELECT 'a', 'b' UNION ALL SELECT 'a', 'c'
Now run an update:
UPDATE myTest set Field2 = 'b' WHERE Field1 = 'a'