SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


UPDATE() and triggers


UPDATE() and triggers

Author
Message
ronmoses
ronmoses
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1724 Visits: 1011
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
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1979 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.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2349 Visits: 474
"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 (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)

Group: General Forum Members
Points: 705 Visits: 270
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
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19219 Visits: 12426
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
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1979 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 (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)

Group: General Forum Members
Points: 705 Visits: 270
Okay..thank you...:-D

Anil Kubireddi
sjimmo
sjimmo
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4892 Visits: 2907
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 (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)

Group: General Forum Members
Points: 338 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 Guru
SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)

Group: Administrators
Points: 151390 Visits: 19455
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