Enforcing multiple columns updated thru trigger

Question

Enforcing multiple columns updated thru trigger

AVB

SSChampion

Points: 11418
More actions
January 29, 2010 at 2:31 pm

#72823

I am trying to create a trigger that will fail an update to a specific table when a certain column isn't updated as well as the others.
If the "Rate" or "Quantity" columns are updated then the "Modified" Column must also be updated with a value.
I'm not quite sure how to do this with a trigger. I can't get the Modified value from the Inserted table because there is already data in the "Modified" column for most rows. And the update will just reuse that value. I'm looking for a new update to that column regardless if it is the same value or not.
So far I've got something like:
Created Trigger....
FOR UPDATE...
IF update(Quantity) or Update(Rate)
BEGIN
IF update(Modifed) BEGIN ??? END
ELSE
RAISERROR....
END
Any ideas?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

AVB (1/29/2010)
I'm not quite sure how to do this with a trigger. I can't get the Modified value from the Inserted table because there is already data in the "Modified" column for most rows. And the update will just reuse that value. I'm looking for a new update to that column regardless if it is the same value or not.

It sounds like you may not have a good understanding of how the UPDATE() function works. Basically, UPDATE(<columnname>) will return true if the column specified is assigned a value in an update statement... regardless of whether the data itself changed, or even if the numbers of rows being updated = 0. All it means is that an update statement ran, where the column specified was on the left-hand side of the columns being updated.

If the update statement were (this example updates the columns with the same values... but the where clause means NO ROWS will be updated!):

UPDATE dbo.MyTable

SET Quantity = Quantity,

Rate = Rate

WHERE 1=2 --<< LOOK! no rows will be updated!

Then UPDATE(Quantity) = 1, UPDATE(Rate) = 1 and UPDATE(Modified) = 0

So, for your trigger, this will raise an error (failing the update) if either the Quantity or Rate columns are modified, but the Modified column isn't:

IF (UPDATE(Quantity) or UPDATE(Rate)) AND NOT UPDATE(Modified) RAISERROR(.....)

HTH,

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

AVB SSChampion Points: 11418 More actions · Answer 2

Wayne,

Thanks for the reply. The "NOT UPDATE" was exactly what I was looking for. I didn't think of trying that. I also didn't realize that if an update doesn't actually occur that the trigger would still be invoked.