SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Ron Moses / UPDATE() and triggers / Latest Posts

RE: UPDATE() and triggers

Muhammed Rafiq — Wed, 09 Dec 2009 04:02:31 GMT

How did u get it?

RE: UPDATE() and triggers

Bhavesh_Patel — Wed, 09 Dec 2009 03:54:42 GMT

was tricky ... but i got it

RE: UPDATE() and triggers

Muhammed Rafiq — Wed, 09 Dec 2009 02:48:29 GMT

I also get the error: (1 row(s) affected)Msg 102, Level 15, State 1, Line 3Incorrect syntax near 'Value'.

RE: UPDATE() and triggers

john.arnott — Wed, 02 Dec 2009 16:51:05 GMT

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. [code="sql"]-- 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))GOINSERT INTO TriggerTest VALUES (1, 'Hello')GOCREATE 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'ENDGOUPDATE TriggerTest SET Text = Text[/code]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: [quote]UPDATE() returns TRUE regardless of whether an INSERT or UPDATE attempt is successful.[/quote]Actually, it fires [i]even if no rows are found to update[/i], 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):[code="sql"]--See trigger fire with 0 rows affectedUPDATE TriggerTest SET Value = Valuewhere value = 7[/code] ...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.[code="sql"]--See an error prevent the update and thereby the triggerUPDATE TriggerTest SET Value = 'Value'[/code]----edit: minor typo

RE: UPDATE() and triggers

David in .AU — Wed, 02 Dec 2009 15:02:00 GMT

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 :)

RE: UPDATE() and triggers

Hugo Kornelis — Wed, 02 Dec 2009 13:04:40 GMT

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.

RE: UPDATE() and triggers

sjimmo — Wed, 02 Dec 2009 10:45:04 GMT

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.

RE: UPDATE() and triggers

Steve Jones - SSC Editor — Wed, 02 Dec 2009 09:13:14 GMT

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.

RE: UPDATE() and triggers

chriscoates — Wed, 02 Dec 2009 07:35:50 GMT

[quote][b]Tao Klerks (12/2/2009)[/b][hr]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.[/quote]Now THAT's something I didn't know about but should have. Perhaps the question would have been better if the UPDATE statement was:[code="sql"]UPDATE TriggerTestSET Value = VALUEWHERE VALUE = 0;[/code]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.[code="sql"]CREATE TABLE TriggerTest (Value int)GOINSERT INTO TriggerTest VALUES (1)GOCREATE TRIGGER tr_TriggerTest ON TriggerTest AFTER UPDATEAS 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' ENDENDGO--Set Value equal to itselfUPDATE TriggerTestSET Value = VALUEWHERE VALUE = 0;DROP TABLE TriggerTest;[/code]

RE: UPDATE() and triggers

sjimmo — Wed, 02 Dec 2009 06:40:05 GMT

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

RE: UPDATE() and triggers

Anil KK — Wed, 02 Dec 2009 06:19:50 GMT

Okay..thank you...:-D

RE: UPDATE() and triggers

Tao Klerks — Wed, 02 Dec 2009 06:17:58 GMT

[quote][b]vkirkpat (12/2/2009)[/b][hr]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.[/quote]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.[quote][b] anil.kubireddi (12/2/2009)[/b][hr]I have tried this and got fallowing error,Server: Msg 245, Level 16, State 1, Line 1Syntax 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.[/quote]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?

RE: UPDATE() and triggers

Hugo Kornelis — Wed, 02 Dec 2009 06:13:49 GMT

[quote][b]anil.kubireddi (12/2/2009)[/b][hr]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)GOINSERT INTO TriggerTest VALUES (1)GOCREATE 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'ENDGO--Set Value equal to itselfUPDATE TriggerTestSET Value = Value;DROP TABLE TriggerTest;=======================I have tried this and got fallowing error,Server: Msg 245, Level 16, State 1, Line 1Syntax 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. [/quote]Hi Anil,Is there any possibility that you executedUPDATE TriggerTestSET Value = 'Value';instead ofUPDATE TriggerTestSET Value = Value;

RE: UPDATE() and triggers

Anil KK — Wed, 02 Dec 2009 06:03:41 GMT

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)GOINSERT INTO TriggerTest VALUES (1)GOCREATE 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'ENDGO--Set Value equal to itselfUPDATE TriggerTestSET Value = Value;DROP TABLE TriggerTest;=======================I have tried this and got fallowing error,Server: Msg 245, Level 16, State 1, Line 1Syntax 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.

RE: UPDATE() and triggers

Victor Kirkpatrick — Wed, 02 Dec 2009 05:38:27 GMT

"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.

RE: UPDATE() and triggers

Tao Klerks — Wed, 02 Dec 2009 02:29:12 GMT

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.

UPDATE() and triggers

ronmoses — Tue, 01 Dec 2009 20:26:10 GMT

Comments posted to this topic are about the item [B]UPDATE() and triggers[/B]