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...
EXAMPLE
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'