August 9, 2018 at 8:38 pm
Comments posted to this topic are about the item The UPDATE() function
August 10, 2018 at 1:48 am
tricksy...
Nice question,thanks Steve
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
August 10, 2018 at 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?
August 10, 2018 at 5:32 am
sipas - Friday, August 10, 2018 3:41 AMNot 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.
August 10, 2018 at 5:40 am
Vladan - Friday, August 10, 2018 5:32 AMsipas - Friday, August 10, 2018 3:41 AMNot 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...
August 10, 2018 at 7:39 am
sipas - Friday, August 10, 2018 5:40 AMVladan - Friday, August 10, 2018 5:32 AMsipas - Friday, August 10, 2018 3:41 AMNot 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.
August 10, 2018 at 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.
Be still, and know that I am God - Psalm 46:10
August 10, 2018 at 7:50 am
Vladan - Friday, August 10, 2018 5:32 AMsipas - Friday, August 10, 2018 3:41 AMNot 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 🙂
August 10, 2018 at 7:52 am
timwell - Friday, August 10, 2018 7:39 AMsipas - Friday, August 10, 2018 5:40 AMI 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.
August 11, 2018 at 2:20 am
david.gugg - Friday, August 10, 2018 7:45 AMThat 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
...
August 13, 2018 at 10:36 am
lmalatesta - Friday, August 10, 2018 7:52 AMtimwell - Friday, August 10, 2018 7:39 AMsipas - Friday, August 10, 2018 5:40 AMI 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.
Comparing inserted and deleted tables is further made confusing by the requirement to "pair up" rows from the inserted and deleted tables, since multiple records can be modified in a single update. One might normally do this pairing up using the primary key column(s), but keep in mind that it is possible to modify the primary key column. It is even possible to modify the primary key column for PKs that serve as foreign keys, going so far as to swap primary key values between two rows in a single UPDATE statement. Furthermore, if one has CASCADE UPDATE turned on for the foreign keys, than the child tables will swap their foreign key values. If one doesn't have CASCADE UPDATE turned on for the foreign keys, then the child tables will retain their original foreign key values.
August 13, 2018 at 2:36 pm
clarified the question to note an attempt to change, successful or not, rather than "changed"
August 14, 2018 at 7:58 am
t.ovod-everett - Monday, August 13, 2018 10:36 AMComparing inserted and deleted tables is further made confusing by the requirement to "pair up" rows from the inserted and deleted tables, since multiple records can be modified in a single update. One might normally do this pairing up using the primary key column(s), but keep in mind that it is possible to modify the primary key column. It is even possible to modify the primary key column for PKs that serve as foreign keys, going so far as to swap primary key values between two rows in a single UPDATE statement. Furthermore, if one has CASCADE UPDATE turned on for the foreign keys, than the child tables will swap their foreign key values. If one doesn't have CASCADE UPDATE turned on for the foreign keys, then the child tables will retain their original foreign key values.
All that is true. This is why in my experience, one should always use arbitrary values for primary keys (e.g. an IDENTITY column) and if one needs a more "user friendly" key, add it on to the table.
This also helps indexes from getting fragmented.
Also, triggers that assume that only a single record will get updated are one of my favorite things to hate.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply