March 26, 2009 at 1:00 pm
I have a delete trigger and an update trigger on a table. The delete trigger actually updates data in the table and thus calls the update trigger. Is there a way to prevent the update trigger from firing when the update is from the delete trigger?
TIA
John
John Deupree
March 26, 2009 at 3:32 pm
Hi,
I just tried this out...
alter trigger tdTest on dbo.Test
FOR DELETE
AS
BEGIN
disable trigger tuTest on dbo.Test;
UPDATE test SET number = 10 where number2 = 1;
enable trigger tuTest on dbo.Test;
END
...and it works. The update trigger is not fired but for the next update statement it works...
...but I don't know what happens if you have concurrent users 🙁
Hope that helps 🙂
-----------------------
SQL Server Database Copy Tool at Codeplex
March 27, 2009 at 2:32 am
John Deupree (3/26/2009)
The delete trigger actually updates data in the table and thus calls the update trigger.
Hello,
maybe it would be best to post both triggers and explain why delete trigger updates data in the same table. Maybe it should be done in some other way...
Not knowing what it is about, I could come up with following idea: add column trg_flag BIT with default 0 to your table. In the delete trigger, make sure that you change value in this column (use CASE) and in the update trigger add condition inserted.trg_flag = deleted.trg_flag. However, maybe you already have some means to identify that it is the trigger what caused update and you won't need to add anything, and maybe you can be rid of thie entire problem by writing the triggers differently. That is hard to tell without knowing more.
March 28, 2009 at 11:41 am
Try to handle it with CONTEXT_INFO and a transaction.
Within your DELETE trigger:
-- ...
BEGIN TRANSACTION
SET CONTEXT_INFO 0x01
-- do your update
SET CONTEXT_INFO 0x00
COMMIT TRANSACTION
Within your UPDATE trigger:
IF (CONTEXT_INFO() = 0x01)
RETURN
-- ...
Greets
Flo
March 28, 2009 at 12:12 pm
If you just merge the two triggers together, it will not trigger itself unless you have Recursive triggers enabled.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 28, 2009 at 12:15 pm
Florian:
I would not recommend using CONTEXT_INFO for this (or anything that I can think of). CONTEXT_INFO has no namespace or transactional control so as soon as more than one thing is using at the same time they will start overwriting each other. Bad things follow this...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 28, 2009 at 12:36 pm
Nevertheless my old chief (older C programmer) would say 128byte are quiet much place for BIT masks... 😉
The 0x01 was just an example. Sure it's not nice, but sometimes it just needs to work and this would be a solution. So you can define a unique bit or even byte within the context for this special case.
Anyway, I really hope John only needs this for one or two special cases.
Greets
Flo
March 28, 2009 at 6:06 pm
Ummmm.... the only way a DELETE trigger that updates the table will cause other triggers to fire is if Cascade Triggers is turned on. It's not normally a good idea because it's "hidden" code and you could end up having a single action do nothing but fire triggers all day.
Disabling a trigger from within a trigger probably shouldn't be done either. What if someone does an update to that table while the trigger is disabled? Unless the whole table is locked, you could be asking for a nearly untraceable world of hurt.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 30, 2009 at 9:19 am
Thanks for the reply. I'll try that.
John
John Deupree
March 30, 2009 at 9:28 am
The table is a status table with a begin and end date. Only one status can be the current status and is identified by a null in the end date colunm. Any change in one of the status begin dates updates the end date for the previous status. A deletion of a status updtes the end date of the previous status to null.
Vladan (3/27/2009)
John Deupree (3/26/2009)
The delete trigger actually updates data in the table and thus calls the update trigger.Hello,
maybe it would be best to post both triggers and explain why delete trigger updates data in the same table. Maybe it should be done in some other way...
Not knowing what it is about, I could come up with following idea: add column trg_flag BIT with default 0 to your table. In the delete trigger, make sure that you change value in this column (use CASE) and in the update trigger add condition inserted.trg_flag = deleted.trg_flag. However, maybe you already have some means to identify that it is the trigger what caused update and you won't need to add anything, and maybe you can be rid of thie entire problem by writing the triggers differently. That is hard to tell without knowing more.
John Deupree
March 30, 2009 at 9:29 am
I'm not familiar with CONTEXT_INFO. I'll try it out. Thanks
Florian Reischl (3/28/2009)
Try to handle it with CONTEXT_INFO and a transaction.Within your DELETE trigger:
-- ...
BEGIN TRANSACTION
SET CONTEXT_INFO 0x01
-- do your update
SET CONTEXT_INFO 0x00
COMMIT TRANSACTION
Within your UPDATE trigger:
IF (CONTEXT_INFO() = 0x01)
RETURN
-- ...
Greets
Flo
John Deupree
March 30, 2009 at 9:31 am
I'll see if I can fo this. Thanks
RBarryYoung (3/28/2009)
If you just merge the two triggers together, it will not trigger itself unless you have Recursive triggers enabled.
John Deupree
March 30, 2009 at 9:36 am
Jeff Moden (3/28/2009)
Disabling a trigger from within a trigger probably shouldn't be done either. What if someone does an update to that table while the trigger is disabled? Unless the whole table is locked, you could be asking for a nearly untraceable world of hurt.
The other reason you don't want to do that is that disabling a trigger counts as a DDL change, it requires ALTER permission on the table and it has to take a Sch-M lock in order to run. A Sch-M lock is not compatible with any other operations on the table, it would have to wait until no one was reading the table to run. Doing that within a trigger is asking for blocking and deadlock problems.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 30, 2009 at 9:38 am
Thanks. I was thinking only of the recursive trigger setting which only controls triggers calling themselves. I wasn't aware that the nested trigger setting could control this. I'm working on a dev instance of SQL. I'll have to see what the setting is on the prod instance.
Jeff Moden (3/28/2009)
Ummmm.... the only way a DELETE trigger that updates the table will cause other triggers to fire is if Cascade Triggers is turned on. It's not normally a good idea because it's "hidden" code and you could end up having a single action do nothing but fire triggers all day.Disabling a trigger from within a trigger probably shouldn't be done either. What if someone does an update to that table while the trigger is disabled? Unless the whole table is locked, you could be asking for a nearly untraceable world of hurt.
John Deupree
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply