February 1, 2011 at 10:49 am
Hello - I have a delete trigger on a table in one database that I want to trigger a delete on a table in a different database, both in the same instance of SQL 2000. I want to prevent the first trigger from firing the delete trigger in the second database.
After looking through previous posts I have tried a few variations to filter on nest level but none of them work. first i tried code like this:
ALTER TRIGGER [dbo].[UPDATE_EMAIL_IN_HEAT] ON [dbo].[CONTSUPP]
FOR DELETE
AS
BEGIN TRANSACTION
IF trigger_nestlevel() <1
begin
[snip]
This had no effect, the second trigger kept firing.
More recently I tried
ALTER TRIGGER [dbo].[UPDATE_EMAIL_IN_HEAT] ON [dbo].[CONTSUPP]
FOR DELETE
AS
BEGIN TRANSACTION
IF @@NESTLEVEL > 1
BEGIN
RETURN
END
[snip]
This time the delete doesn't occur in the second db and it locks up for a long period of time.
I would rather not use sp_configure to remove recursive triggers altogether since this is an instance wide change, I don't think it would have an effect but would have to do more digging to know. Instead, I would prefer an approach that recognizes if the deletion is caused by the trigger in DB1 Table 1 and then don't fire the delete trigger in DB2 Table 2 (but DO complete the deletion!) Is there a way to do this with a differnt syntax or expression?
If none of this works I can disable and then enable the triggers in the DB2 as part of the trigger in DB1 (and vice versa), this seems to work fine but it would be preferable to keep them enabled and just don't fire if the delete comes from the trigger in the other DB.
Any help is appreciated - thanks!
February 1, 2011 at 11:03 am
Please don't cross post. It just wastes peoples time and fragments replies.
No replies to this thread please. Direct replies to: http://www.sqlservercentral.com/Forums/Topic1056986-9-1.aspx
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply