UPDATE() and triggers

  • Comments posted to this topic are about the item UPDATE() and triggers

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • A trickier variation could be the same question, without the "Insert" statement.

    Even though there are no rows being updated in that variant, the trigger still finds that the column was being updated.

    http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.

  • "The UPDATE(Column) function returns TRUE even if an UPDATE does not change the value."

    But, the update DOES change the value, right? Even though it may be the same value, it does get updated. It's not like SQL Server says to itself, hey, this value is the same as then one that's already there, so don't even mess with the physical I/O's here.

  • i am refferning to the below question posted,

    What is the output of the following code (disregarding "X row(s) affected"):

    CREATE TABLE TriggerTest (Value int)

    GO

    INSERT INTO TriggerTest VALUES (1)

    GO

    CREATE TRIGGER tr_TriggerTest ON TriggerTest AFTER UPDATEAS BEGIN SET NOCOUNT ON; IF UPDATE(Value) PRINT 'The trigger fired' ELSE PRINT 'The trigger did not fire'END

    GO

    --Set Value equal to itself

    UPDATE TriggerTestSET Value = Value;

    DROP TABLE TriggerTest;

    =======================

    I have tried this and got fallowing error,

    Server: Msg 245, Level 16, State 1, Line 1

    Syntax error converting the varchar value 'Value' to a column of data type int.

    it is said that the trigger gets fired, then why 'The trigger fired' statement doesn't got printed.

    [font="Times New Roman"]Anil Kubireddi[/font]

  • anil.kubireddi (12/2/2009)


    i am refferning to the below question posted,

    What is the output of the following code (disregarding "X row(s) affected"):

    CREATE TABLE TriggerTest (Value int)

    GO

    INSERT INTO TriggerTest VALUES (1)

    GO

    CREATE TRIGGER tr_TriggerTest ON TriggerTest AFTER UPDATEAS BEGIN SET NOCOUNT ON; IF UPDATE(Value) PRINT 'The trigger fired' ELSE PRINT 'The trigger did not fire'END

    GO

    --Set Value equal to itself

    UPDATE TriggerTestSET Value = Value;

    DROP TABLE TriggerTest;

    =======================

    I have tried this and got fallowing error,

    Server: Msg 245, Level 16, State 1, Line 1

    Syntax error converting the varchar value 'Value' to a column of data type int.

    it is said that the trigger gets fired, then why 'The trigger fired' statement doesn't got printed.

    Hi Anil,

    Is there any possibility that you executed

    UPDATE TriggerTest

    SET Value = 'Value';

    instead of

    UPDATE TriggerTest

    SET Value = Value;


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • vkirkpat (12/2/2009)


    But, the update DOES change the value, right? Even though it may be the same value, it does get updated. It's not like SQL Server says to itself, hey, this value is the same as then one that's already there, so don't even mess with the physical I/O's here.

    Right - but even if SQL Server was not doing any update at all (if there were no rows in the table, or if the WHERE clause excluded all rows), it would still consider that the column was updated.

    anil.kubireddi (12/2/2009)


    I have tried this and got fallowing error,

    Server: Msg 245, Level 16, State 1, Line 1

    Syntax error converting the varchar value 'Value' to a column of data type int.

    it is said that the trigger gets fired, then why 'The trigger fired' statement doesn't got printed.

    It looks like the copy/paste from your browser or email client lost some carriage returns ("UPDATEAS" and "UPDATE TriggerTestSET Value = Value;") - is that the cause?

    http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.

  • Okay..thank you...:-D

    [font="Times New Roman"]Anil Kubireddi[/font]

  • Great question - thought provoking. ALMOST got me to say trigger did not fire.

    Steve Jimmo
    Sr DBA
    “If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

  • Tao Klerks (12/2/2009)


    Right - but even if SQL Server was not doing any update at all (if there were no rows in the table, or if the WHERE clause excluded all rows), it would still consider that the column was updated.

    Now THAT's something I didn't know about but should have. Perhaps the question would have been better if the UPDATE statement was:

    UPDATE TriggerTest

    SET Value = VALUE

    WHERE VALUE = 0;

    In this case the trigger still fires, and more importantly "IF UPDATE(Value)" returns true, even though no rows were updated, so actually no values were updated. (Yes, in the original version the Value column is updated from 1 to 1) I think the output text is a bit misleading though. I would have put "Value field was updated" and "Value field was not updated" in the IF block, and another line before the block for "Trigger Fired".

    e.g.

    CREATE TABLE TriggerTest (Value int)

    GO

    INSERT INTO TriggerTest VALUES (1)

    GO

    CREATE TRIGGER tr_TriggerTest

    ON TriggerTest

    AFTER UPDATE

    AS

    BEGIN

    SET NOCOUNT ON;

    PRINT 'The Trigger Fired'

    IF UPDATE(Value)

    BEGIN

    PRINT 'The Value column was updated'

    select * FROM INSERTED

    END

    ELSE

    BEGIN

    PRINT 'The Value column was not updated'

    END

    END

    GO

    --Set Value equal to itself

    UPDATE TriggerTest

    SET Value = VALUE

    WHERE VALUE = 0;

    DROP TABLE TriggerTest;

  • The "change" is from the user point of view. Most people would consider a change from 1 to 1 being no change. However the engine probably does not do a comparison and actually does perform a re-write of the value on disk.

  • SJ - I believe that you are correct. SQL Server is not smart enough to tell you whether the value changed, but that there was an update that did not fail. Thus, the update completed and the trigger fires. It leaves the smarts to the developer to determine ir check and see if the value changed or not.

    Steve Jimmo
    Sr DBA
    “If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

  • Sjimmo and Steve,

    It's like you say, but then it goes even a step further.

    The UPDATE() function does, as you indicate, not check if any values actually changed.

    But it also doe not check if any values were set to some (possibly unchanged) value.

    The only thing UPDATE() tells you is that the column is mentioned at the left-hand side of an =-mark in the SET phrase. So even if the table is empty, the WHERE does not match any rows, or even if the WHERE has an always false predicate, the UPDATE() function will still return TRUE for all columns that appear in the SET clause.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Well, not quite every column that appears in the set line.. the trigger update function is restricted to a single column at a time, but you can chain them together 🙂

  • Great question. It took me thorugh several layers. I had to realize that the trigger could never reach the "did not fire" branch of the if statement. Then, I was fooled (as were many, apparently) by the notion that a null-change would suppress the trigger. But then, a bit of experimentation led to deeper understanding.

    Q: If it "didn't fire", how would it be running the IF statement?

    A: Only if there were another column to be affected. This code does return "The Trigger did not fire" even though it did because the VALUE column was not touched. -- See a trigger say that it didn't fire....

    -- (actually, the trigger fires, but internally it sees

    -- that the tested field VALUE wasn't affected)

    CREATE TABLE TriggerTest (Value int, Text char(10))

    GO

    INSERT INTO TriggerTest VALUES (1, 'Hello')

    GO

    CREATE TRIGGER tr_TriggerTest

    ON TriggerTest

    AFTER UPDATE

    AS BEGIN SET NOCOUNT ON;

    IF UPDATE(Value)

    PRINT 'The trigger fired'

    ELSE

    PRINT 'The trigger did not fire'

    END

    GO

    UPDATE TriggerTest SET Text = Text

    Second, and to the point of several posts here, the trigger fires upon "an attempt" to update the table, but determining what constitutes an update attempt is a bit tricky. Microsoft's documentation is slightly misleading when it says in the link provided:

    UPDATE() returns TRUE regardless of whether an INSERT or UPDATE attempt is successful.

    Actually, it fires even if no rows are found to update, but an error will prevent it from firing, so an unsucessful attempt in that case won't return TRUE. By example, this code returns "The trigger fired" even though it effects no change to the database (not even a wash of updating a field to its original value):--See trigger fire with 0 rows affected

    UPDATE TriggerTest SET Value = Value

    where value = 7 ...but if there's an error, as forced in the following statement, SQL Server doesn't consider the update to have been attempted and so does not fire the trigger.--See an error prevent the update and thereby the trigger

    UPDATE TriggerTest SET Value = 'Value'

    ----

    edit: minor typo

  • I also get the error:

    (1 row(s) affected)

    Msg 102, Level 15, State 1, Line 3

    Incorrect syntax near 'Value'.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply