December 1, 2009 at 8:26 pm
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
December 2, 2009 at 2:29 am
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.
December 2, 2009 at 5:38 am
"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.
December 2, 2009 at 6:03 am
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]
December 2, 2009 at 6:13 am
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;
December 2, 2009 at 6:17 am
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.
December 2, 2009 at 6:19 am
Okay..thank you...:-D
[font="Times New Roman"]Anil Kubireddi[/font]
December 2, 2009 at 6:40 am
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
December 2, 2009 at 7:35 am
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;
December 2, 2009 at 9:13 am
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.
December 2, 2009 at 10:45 am
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
December 2, 2009 at 1:04 pm
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.
December 2, 2009 at 3:02 pm
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 🙂
December 2, 2009 at 4:51 pm
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
December 9, 2009 at 2:48 am
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