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


The UPDATE() function


The UPDATE() function

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (582K reputation)SSC Guru (582K reputation)SSC Guru (582K reputation)SSC Guru (582K reputation)SSC Guru (582K reputation)SSC Guru (582K reputation)SSC Guru (582K reputation)SSC Guru (582K reputation)

Group: Administrators
Points: 582448 Visits: 20877
Comments posted to this topic are about the item The UPDATE() function

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
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59155 Visits: 8013
tricksy...
Nice question,thanks Steve

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
sipas
sipas
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2412 Visits: 821
Not sure about this one - I thought that UPDATE() returned TRUE if an attempt was made to update the column, regardless of whether the update was successful. Chose the right answer because it was the only one that had a Boolean value, but is " true for changes or false for no changes" correct?
Vladan
Vladan
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18629 Visits: 782
sipas - Friday, August 10, 2018 3:41 AM
Not sure about this one - I thought that UPDATE() returned TRUE if an attempt was made to update the column, regardless of whether the update was successful. Chose the right answer because it was the only one that had a Boolean value, but is " true for changes or false for no changes" correct?

Well, I'm not sure about what happens when the update is unsuccessful... I think that if update is unsuccessful, trigger does not fire. But if you update a column with "itself" (not changing the value), UPDATE() returns TRUE. In my opinion, "changes" is generally understood as "there is a difference between 'before' and 'after' ", so the correct answer is rather misleading.
If you want to fire trigger only when a value changes, it must be done by comparing values in DELETED and INSERTED.




sipas
sipas
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2412 Visits: 821
Vladan - Friday, August 10, 2018 5:32 AM
sipas - Friday, August 10, 2018 3:41 AM
Not sure about this one - I thought that UPDATE() returned TRUE if an attempt was made to update the column, regardless of whether the update was successful. Chose the right answer because it was the only one that had a Boolean value, but is " true for changes or false for no changes" correct?

I think that if update is unsuccessful, trigger does not fire.

I thought UPDATE() was meant to tell you if an attempt was made to update the column, so it would be TRUE even if the update is unsuccessful - not sure though...

timwell
timwell
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4012 Visits: 3865
sipas - Friday, August 10, 2018 5:40 AM
Vladan - Friday, August 10, 2018 5:32 AM
sipas - Friday, August 10, 2018 3:41 AM
Not sure about this one - I thought that UPDATE() returned TRUE if an attempt was made to update the column, regardless of whether the update was successful. Chose the right answer because it was the only one that had a Boolean value, but is " true for changes or false for no changes" correct?

I think that if update is unsuccessful, trigger does not fire.

I thought UPDATE() was meant to tell you if an attempt was made to update the column, so it would be TRUE even if the update is unsuccessful - not sure though...

According to the remarks section in the reference provided:
UPDATE() returns TRUE regardless of whether an INSERT or UPDATE attempt is successful.

david.gugg
david.gugg
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4520 Visits: 1240
That was tricky because my brain is trained to think of 1 as true and 0 as false. I read the last to answers as the same before I had to stop and think about it for a second.


Personal blog relating fishing to database administration:

https://davegugg.wordpress.com/
Budd
Budd
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2460 Visits: 817
Vladan - Friday, August 10, 2018 5:32 AM
sipas - Friday, August 10, 2018 3:41 AM
Not sure about this one - I thought that UPDATE() returned TRUE if an attempt was made to update the column, regardless of whether the update was successful. Chose the right answer because it was the only one that had a Boolean value, but is " true for changes or false for no changes" correct?

Well, I'm not sure about what happens when the update is unsuccessful... I think that if update is unsuccessful, trigger does not fire. But if you update a column with "itself" (not changing the value), UPDATE() returns TRUE. In my opinion, "changes" is generally understood as "there is a difference between 'before' and 'after' ", so the correct answer is rather misleading.
If you want to fire trigger only when a value changes, it must be done by comparing values in DELETED and INSERTED.

WOW - First of all - GREAT QOD Question.. Secondly VERY INTERESTING discussion topic posed here.
While the field value has not changed, it would appear that the record has been altered, as would be indicated by a ModifiedOn DATETIME field.
And when EXACTLY does the update trigger fire ? Can an update fail before that trigger fires ?
---And here is where I would like to leave you with some bit of great wisdom or intelligence, however I lack that ability :-)

lmalatesta
lmalatesta
SSC Veteran
SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)

Group: General Forum Members
Points: 259 Visits: 130
timwell - Friday, August 10, 2018 7:39 AM
sipas - Friday, August 10, 2018 5:40 AM

I thought UPDATE() was meant to tell you if an attempt was made to update the column, so it would be TRUE even if the update is unsuccessful - not sure though...

According to the remarks section in the reference provided:
UPDATE() returns TRUE regardless of whether an INSERT or UPDATE attempt is successful.

The confusion is because UPDATE is unintuitive. It returns true if a column name was named in an an update statement regardless of whether the value has changed.

So if column x has a value of 1 and the command UPDATE SET x = 1 is run, UPDATE will return true even though the value hasn't changed.

Theory wise this is probably the correct. In practice, most people are usually only interested in whether the value has changed from the previous value. So in the example above, we don't care if the UPDATE statement set the value of x to 1. We care if the value of x has changed to something other than 1.

The only way to determine that is to compare the inserted and deleted tables.

HappyGeek
HappyGeek
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14000 Visits: 6667
david.gugg - Friday, August 10, 2018 7:45 AM
That was tricky because my brain is trained to think of 1 as true and 0 as false. I read the last to answers as the same before I had to stop and think about it for a second.


+1

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