Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


UPDATE() and triggers


UPDATE() and triggers

Author
Message
ronmoses
ronmoses
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1093 Visits: 996
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

Tao Klerks
Tao Klerks
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1393 Visits: 1249
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.
Victor Kirkpatrick
Victor Kirkpatrick
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2128 Visits: 446
"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.
Anil KK
Anil KK
Say Hey Kid
Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)

Group: General Forum Members
Points: 695 Visits: 265
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
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8321 Visits: 11558
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
Tao Klerks
Tao Klerks
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1393 Visits: 1249
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.
Anil KK
Anil KK
Say Hey Kid
Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)

Group: General Forum Members
Points: 695 Visits: 265
Okay..thank you...:-D

Anil Kubireddi
sjimmo
sjimmo
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3346 Visits: 2826
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
chriscoates
chriscoates
Old Hand
Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)

Group: General Forum Members
Points: 308 Visits: 110
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;


Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36089 Visits: 18738
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
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search