TSQL After Update Trigger check for update on multiple columns in one IF UPDATE
Solution 1
This is a bit long for a comment.
UPDATE()
is a function used in a trigger. There is not specific syntax as if UPDATE()
. As far as I know, triggers allow you to combine multiple conditions in an if
statement.
But, even more to the point, read the documentation on UPDATE()
. The very example used in the documentation is:
IF ( UPDATE (StateProvinceID) OR UPDATE (PostalCode) )
BEGIN
RAISERROR (50009, 16, 10)
END;
So, use the correct syntax for yours and you will be fine:
IF UPDATE(ColumnA) OR UPDATE(ColumnB) -- if ColumnA or ColumnB updates start
begin
UPDATE Table
SET ColumnC = ColumnA + ColumnB -- recalculate Column C
end
However, I would suggest that you use a computed column instead.
Solution 2
You can use COLUMNS_UPDATED
:
Returns a
varbinary
bit pattern that indicates the columns in a table or view that were inserted or updated.
And just check for any of the bits you're interested in being set using bitwise operators:
IF (COLUMNS_UPDATED() & CAST(0x0A as int)) != 0
begin
--column 2 or 4 was updated
end
Of course, this does then depend on the column order, which is usually a bad thing to depend upon, and it also won't blow up if the column order is later changed.
J3FFK
Updated on June 26, 2022Comments
-
J3FFK about 2 years
The basic syntax for an After Update trigger in TSQL is:
CREATE TRIGGER [dbo].[triggerName] ON [dbo].[Table] AFTER UPDATE, INSERT --trigger when Update or Insert in table AS BEGIN SET NOCOUNT ON; IF UPDATE (ColumnA) -- if ColumnA updates start begin UPDATE Table SET ColumnC = ColumnA + ColumnB -- recalculate Column C end IF UPDATE (ColumnB) -- if ColumnB updates start begin UPDATE Table SET ColumnC = ColumnA + ColumnB -- recalculate Column C end END
Now above could work I guess, but is it possible to combine both IF UPDATES in one?: Something like this:
IF UPDATE (ColumnA) OR (ColumnB) -- if ColumnA or ColumnB updates start begin UPDATE Table SET ColumnC = ColumnA + ColumnB -- recalculate Column C end
You could use a computed column of course, but out of curiosity I'd like to know if you can check for updates on multiple columns at once in a trigger and then do the after update trigger modification.