April 27, 2011 at 7:39 am
I am looking for examples of an UPDATE query that will only fire if certain columns in a table are updated. I have a table that has around 25 columns, but I only what the trigger to fire if Column 9, Column 10, and Column 11 are updated. Does anyone have a good example of this or can anyone point me in the right in the direction on this? Any and all help will be greatly appreciated.
Please let me know if you need more information.
Thanks
April 27, 2011 at 7:43 am
I'm sure if you look in Books Online you'll find what you're looking for. As far as I know, you can't specify that the trigger only fire if a particular column is updated, but by comparing the Inserted and Deleted virtual tables, you can put some logic in so that the trigger only performs any actions if that column is updated.
John
April 27, 2011 at 7:49 am
If you wrap the trigger code in an IF statement, you can at least have the trigger "do nothing" unless the specified column is updated.
As in ...
IF UPDATE(columnName)
BEGIN
...
trigger code here
...
END
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 27, 2011 at 7:50 am
I think you're looking for something like this:
CREATE TRIGGER trigger_test ON test FOR UPDATE
AS
SET NOCOUNT ON
IF UPDATE(column9) OR UPDATE(column10) OR UPDATE(column11)
BEGIN
--do your main DML here
END
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
April 27, 2011 at 7:53 am
Thanks everyone for the replies, I have been Googling this as well and think I should be good to go. My only other problem is that my "Advanced" tsql skills are not up to par, but I will get it done.
Thanks Again...
April 30, 2011 at 11:16 am
Just be aware that IF UPDATE does NOT check if the value is changed, only if it is updated (i.e. in an update statement). So if you had
Row: A=1, B=2
update table set B=2 where A=1
the trigger will fire, the IF UPDATE(B) will be true.
To actually know if the value changed is a different problem requiring a join over inserted and deleted, as far as I know (if there is a better way to tell if a column changed let me know). And if they are nullable columns don't forget you have to check that, e.g.
nullif(inserted.b,deleted.b) is not null or nullif(deleted.b,inserted.b) is not null
(Yuck, but it works)
April 30, 2011 at 11:26 am
Ferguson (4/30/2011)
Just be aware that IF UPDATE does NOT check if the value is changed, only if it is updated (i.e. in an update statement). So if you hadRow: A=1, B=2
update table set B=2 where A=1
the trigger will fire, the IF UPDATE(B) will be true.
To actually know if the value changed is a different problem requiring a join over inserted and deleted, as far as I know (if there is a better way to tell if a column changed let me know). And if they are nullable columns don't forget you have to check that, e.g.
nullif(inserted.b,deleted.b) is not null or nullif(deleted.b,inserted.b) is not null
(Yuck, but it works)
It also doesnt check if any rows were updated. The inserted table could be empty yet the code would fire.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply