I have table with 42 columns and a trigger which should do some stuff when 38 of these columns are updated. So, I need to skip the logic if the rest 4 columns are changed.
I can use UPDATE() function and create one big IF
condition, but prefer to do something shorter. Using COLUMNS_UPDATED I can check if all of certain columns are updated?
For example, checking if column 3, 5 and 9 are updated:
IF
(
(SUBSTRING(COLUMNS_UPDATED(),1,1) & 20 = 20)
AND
(SUBSTRING(COLUMNS_UPDATED(),2,1) & 1 = 1)
)
PRINT 'Columns 3, 5 and 9 updated';
So, value 20
for column 3
and 5
, and value 1
for column 9
because it is set in the first bit of the second byte. If I change the statement to OR
it will check if columns 3
and 5
or column 9
is/are updated?
How can apply OR
logic in the context of one byte?
Best Answer
You can use
CHECKSUM()
as a fairly simple methodology for comparing actual values to see if they were changed.CHECKSUM()
will generate a checksum across a list of passed-in values, of which the number and type are indeterminate. Beware, there is a small chance comparing checksums like this will result in false negatives. If you cannot deal with that, you can useHASHBYTES
instead1.The example below uses an
AFTER UPDATE
trigger to retain a history of modifications made to theTriggerTest
table only if either of the values in theData1
orData2
columns change. IfData3
changes, no action is taken.If you're insistent on using the COLUMNS_UPDATED() function, you should not hard-code the ordinal value of the columns in question, since the table definition may change, which may invalidate hard-coded value(s). You can calculate what the value should be at runtime using the system tables. Be aware that the
COLUMNS_UPDATED()
function returns true for the given column bit if the column is modified in ANY row affected by theUPDATE TABLE
statement.This demo inserts rows into the history table that perhaps should not be inserted. The rows have had their
Data1
column updated for some rows, and have had theData3
column updated for some rows. Since this is a single statement, all rows are processed by a single pass through the trigger. Since some rows haveData1
updated, which is part of theCOLUMNS_UPDATED()
comparison, all rows seen by the trigger are inserted into theTriggerHistory
table. If this is "incorrect" for your scenario, you may need to handle each row separately, using a cursor.The
TriggerResult
table now has some potentially misleading rows that look like they don't belong since they show absolutely no changes (to the two columns in that table). In the 2nd set of rows in the image below, TriggerTestID 7 is the only one that looks like it was modified. The other rows only had theData3
column updated; however since the one row in the batch hadData1
updated, all rows are inserted in theTriggerResult
table.Alternately, as @AaronBertrand and @srutzky pointed out, you can perform an comparison of the actual data in the
inserted
anddeleted
virtual tables. Since the structure of both tables is identical, you can use anEXCEPT
clause in the trigger to capture rows where the precise columns you are interested in have changed:1 - see https://stackoverflow.com/questions/297960/hash-collision-what-are-the-chances for a discsussion of the vanishingly small chance that the HASHBYTES calculation may also result in collisions. Preshing has a decent analysis of this problem as well.