TSQL After Update Trigger checks for updates for multiple columns in a single IF UPDATE

The basic syntax for the 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 the above may work, I think, 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 

Of course, you could use the computed column, but out of curiosity I would like to know if you can check for updates on several columns at once in the trigger, and then modify the update trigger.

+4
source share
2 answers

This is a bit longer for comment.

UPDATE() - , . if UPDATE(). , if.

, , UPDATE(). , :

IF ( UPDATE (StateProvinceID) OR UPDATE (PostalCode) )
BEGIN
    RAISERROR (50009, 16, 10)
END;

, , :

IF UPDATE(ColumnA) OR UPDATE(ColumnB) -- if ColumnA or ColumnB updates start
    begin
        UPDATE Table
        SET ColumnC = ColumnA + ColumnB -- recalculate Column C
    end 

, .

+4

COLUMNS_UPDATED:

varbinary, , .

, - , , :

IF (COLUMNS_UPDATED() & CAST(0x0A as int)) != 0
begin
   --column 2 or 4 was updated
end

, , , , .

+2

All Articles