How does if update() statement work in update trigger in SQL server?

Question

update: I update changed values in a column specifically in the other SQL script. So IF UPDATE(column_name) statement should work. For example, When I changed LATTITUDE AND LONGITUDE column in the same row, the if update() can capture the value changes while other if update(column_name) will return false as I did not update those unchanged values. But the problem is, I wish column GROUP_ID to be the same SEQUENCE value as those changes are in same row. The SEQUENCE value should only goes up when hitting next row. However, in my script, there is no way to store current SEQUENCE value in a variable.

I want to group those value changes in columns into SAME SEQUENCE value if in one row otherwise increasing sequence value. However, SEQUENCE values always increases, is it because this update trigger fired more than once if multiple columns changed?

@COUNT_update is used to store current sequence value. How do I fix it up then? thanks

ALTER TRIGGER [dbo].[AUDIT_SITE_UPDATE] ON [dbo].[SITE] 
AFTER UPDATE
AS
BEGIN
    DECLARE @SITE_ID                [INT]
    DECLARE @SITE_DESCRIPTION       [varchar](1000) 
    DECLARE @SOURCE_SITE_NUMBER     [varchar](50) 
    DECLARE @LATTITUDE              [numeric](38, 10) 
    DECLARE @LONGITUDE              [numeric](38, 10) 
    DECLARE @INSERT_TIME            datetime2
    DECLARE @INSERT_USER            [varchar](256)
    DECLARE @seq_Next_Val INT=SELECT NEXT VALUE FOR Audit_Seq, 
    -- UPDATE ROW

        if exists(select * from inserted) and exists(select * from deleted)
        BEGIN

        declare @COUNT_update int;
        set @COUNT_update =convert(int, (select current_value FROM sys.sequences WHERE name = 'Audit_Seq')) ;

        if UPDATE(SITE_DESCRIPTION) 
            BEGIN   
                SELECT  @site_id=SITE_ID,
                        @SOURCE_SITE_NUMBER=SOURCE_SITE_NUMBER,
                        @SITE_DESCRIPTION=SITE_DESCRIPTION,
                        @LATTITUDE=LATTITUDE,
                        @LONGITUDE=LONGITUDE,
                        @INSERT_TIME=INSERT_TIME,
                        @INSERT_USER=INSERT_USER 
                FROM deleted;
                        INSERT INTO  AUDIT_SITE(site_id,GROUP_ID,ACTIVITY,SOURCE_SITE_NUMBER,COLUMN_NAME,OLD_VALUE,NEW_VALUE,INSERT_TIME,INSERT_USER) 
                    VALUES( @SITE_ID,@COUNT_update,'UPDATE',@SOURCE_SITE_NUMBER,'SITE_DESCRIPTION',(SELECT SITE_DESCRIPTION FROM deleted)
                    ,(SELECT SITE_DESCRIPTION FROM inserted),@INSERT_TIME,@INSERT_USER)

            END

         if UPDATE(LATTITUDE)

            BEGIN   

                SELECT  @site_id=SITE_ID,
                        @SOURCE_SITE_NUMBER=SOURCE_SITE_NUMBER,
                        @SITE_DESCRIPTION=SITE_DESCRIPTION,
                        @LATTITUDE=LATTITUDE,
                        @LONGITUDE=LONGITUDE,
                        @INSERT_TIME=INSERT_TIME,
                        @INSERT_USER=INSERT_USER 
                FROM deleted;
                        INSERT INTO  AUDIT_SITE(site_id,GROUP_ID,ACTIVITY,SOURCE_SITE_NUMBER,COLUMN_NAME,OLD_VALUE,NEW_VALUE,INSERT_TIME,INSERT_USER) 
                    VALUES( @SITE_ID,@COUNT_update,'UPDATE',@SOURCE_SITE_NUMBER,'LATTITUDE',(SELECT LATTITUDE FROM deleted)
                    ,(SELECT LATTITUDE FROM inserted),@INSERT_TIME,@INSERT_USER)

            END
        if UPDATE(LONGITUDE)
            BEGIN

                SELECT @site_id=SITE_ID,@SOURCE_SITE_NUMBER=SOURCE_SITE_NUMBER,@SITE_DESCRIPTION=SITE_DESCRIPTION
            ,@LATTITUDE=LATTITUDE,@LONGITUDE=LONGITUDE,@INSERT_TIME=INSERT_TIME,@INSERT_USER=INSERT_USER FROM deleted;
                        INSERT INTO  AUDIT_SITE(site_id,GROUP_ID,ACTIVITY,SOURCE_SITE_NUMBER,COLUMN_NAME,OLD_VALUE,NEW_VALUE,INSERT_TIME,INSERT_USER) 
                    VALUES( @SITE_ID,@COUNT_update,'UPDATE',@SOURCE_SITE_NUMBER,'LONGITUDE',(SELECT LONGITUDE FROM deleted)
                    ,(SELECT LONGITUDE FROM inserted),@INSERT_TIME,@INSERT_USER)            
            END

        END
END

This trigger is badly broken. Triggers don't fire once per column. They don't fire once per *row* either. They fire once per *statement*, which means `inserted` and `deleted` can contain 0, 1 or **multiple** rows. They resemble tables. They should be used *as tables*. Not to obtain the values from arbitrary rows to set scalar variables. — Damien_The_Unbeliever, Apr 15 '19 at 06:46
Checking the number of rows in `inserted` and `deleted` is pointless as well. If a trigger on `UPDATE` fired, then both objects will contain at least 1 row (and the same number of rows). — Thom A, Apr 15 '19 at 07:01
You can't rely on `UPDATE(expressionHere)` to detect changes, it just indicate if the UPDATE updates a column, it can't detect if the column's value changes. Some ORMs even oversets all the columns even if a column's value didn't change at all, hence your `UPDATE(SITE_DESCRIPTION` will be always true. You need to use `inserted.column IS DISTINCT FROM deleted.column` instead of `UPDATE(expressionHere)`, or some emulation of that: https://stackoverflow.com/questions/12456737/does-sql-server-support-is-distinct-from-clause — Michael Buen, Apr 15 '19 at 07:38
The shortest, most readable way to check if a column was changed in an update trigger is this: `iif(inserted.Column = deleted.Column OR(inserted.Column is null and deleted.Column is null), 'equal', 'different')` — Zohar Peled, Apr 15 '19 at 09:09
@ZoharPeled - I really wish Microsoft would hurry up and add `IS [NOT] DISTINCT FROM` as defined by the SQL standard. — Damien_The_Unbeliever, Apr 15 '19 at 09:44
@Damien_The_Unbeliever Don't hold your breath.... [last comment on this subject](https://feedback.azure.com/forums/908035-sql-server/suggestions/32889145-add-language-and-optimizer-support-for-iso-distin) dates back to two years ago : "Don’t you love how useful M$ Connect is for raising issues? Almost 10 years go by after someone at Microsoft says this is a “Good Idea” and yet, no results …." — Zohar Peled, Apr 18 '19 at 07:06

How does if update() statement work in update trigger in SQL server?

0 Answers0