Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

UPDATE() and triggers Expand / Collapse
Author
Message
Posted Tuesday, December 1, 2009 8:26 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 6:55 AM
Points: 880, Visits: 876
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
Post #827165
Posted Wednesday, December 2, 2009 2:29 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 12:38 PM
Points: 1,385, Visits: 1,249
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.
Post #827269
Posted Wednesday, December 2, 2009 5:38 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, October 16, 2014 5:41 AM
Points: 1,831, Visits: 368
"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.
Post #827351
Posted Wednesday, December 2, 2009 6:03 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, April 10, 2014 9:41 AM
Points: 695, Visits: 264
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.


Anil Kubireddi
Post #827375
Posted Wednesday, December 2, 2009 6:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:27 AM
Points: 6,048, Visits: 8,327
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #827386
Posted Wednesday, December 2, 2009 6:17 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 12:38 PM
Points: 1,385, Visits: 1,249
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.
Post #827393
Posted Wednesday, December 2, 2009 6:19 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, April 10, 2014 9:41 AM
Points: 695, Visits: 264
Okay..thank you...

Anil Kubireddi
Post #827396
Posted Wednesday, December 2, 2009 6:40 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:33 AM
Points: 2,917, Visits: 2,532
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
Post #827410
Posted Wednesday, December 2, 2009 7:35 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 9, 2012 7:25 AM
Points: 298, Visits: 107
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;

Post #827458
Posted Wednesday, December 2, 2009 9:13 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 12:34 PM
Points: 31,181, Visits: 15,626
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #827599
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse