Trying to avoid recursive firing of delete triggers - please help!

Question

Post reply

Trying to avoid recursive firing of delete triggers - please help!

ae_from_pa

Old Hand

Points: 346
More actions
February 1, 2011 at 10:52 am

#142282

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!
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Steve Jones - SSC Editor SSC Guru Points: 738093 More actions · Answer 1

why did you begin a transaction in you second try? you're already within a transaction.

ae_from_pa Old Hand Points: 346 More actions · Answer 2

I was just copying a snippet I saw somewhere else. If you think it will work without the BEGIN and END I will try that.

Steve Jones - SSC Editor SSC Guru Points: 738093 More actions · Answer 3

Steve Jones - SSC Editor

SSC Guru

Points: 738093

February 2, 2011 at 12:32 pm

#1281602

Not "Begin", "Begin Transaction" isn't needed.

ae_from_pa Old Hand Points: 346 More actions · Answer 4

Here's some context. The T-SQL for altering the existing trigger has this block of code:

========

ALTER TRIGGER [dbo].[UPDATE_EMAIL_IN_HEAT] ON [dbo].[CONTSUPP]

FOR DELETE

AS

BEGIN TRANSACTION

IF @@NESTLEVEL > 1

BEGIN

RETURN

END

--Scenario 1

begin

update heat_test.gmdev.Contacts set emailID = null

where custid in

(select accountno from deleted where

deleted.contact = 'E-mail Address'

and deleted.mergecodes = 'HEAT1'

and deleted.linkacct is null)

and contacttype='Primary Contact'

--update log for scenario 1

insert into gm_heat_deletions_log

(trigger_name,gm_account,heat_custid,contsupref, gm_rectype,trigger_time,by_whom)

select 'UPDATE_EMAIL_IN_HEAT',accountno,accountno,contsupref, 'DELETE EMAIL FOR PRIMARY',getDATE(),HOST_NAME()

from deleted where

deleted.contact = 'E-mail Address'

and deleted.mergecodes = 'HEAT1'

and deleted.linkacct is null

end

=========

there are a bunch more begin...end sections, then the TSQL ends with COMMIT.

Are you saying I should put the code

IF @@NESTLEVEL > 1

BEGIN

RETURN

END

before BEGIN TRANSACTION - or in place of it? I suspect I am not using the write syntax in the right order.

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 5

I think Steve was trying to tell you that the BEGIN TRANSACTION is not necessary at all. Once you are inside the trigger, you are automatically inside a transaction anyway.

I'm not going to be much help, as I'm not proposing a solution to your problem, but I would like to point out a potential pitfall in your proposed solution.

I would steer clear of using @@NESTLEVEL in this way or you may find the trigger in the 2nd database not firing when you would expect it to. For example:-

UPDATE CONTSUPP blah blah blah

This would fire your trigger and it would go ahead and do its business, but if I update the table from inside a stored procedure:-

CREATE PROC X

AS

UPDATE CONTSUPP blah blah blah

GO

EXEC X

The trigger will fire, but your check for @@NESTLEVEL will incorrectly stop it from doing its business.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 6

Hi, did you try this?

IF TRIGGER_NESTLEVEL()>1 RETURN

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 7

mister.magoo (2/3/2011)
Hi, did you try this?
IF TRIGGER_NESTLEVEL()>1 RETURN

A bit less risky, but could still suffer from similar problems to @@NESTLEVEL if the trigger is ultimately fired as a result of updates from a trigger on another table in the same database.

I may be being a bit paranoid, but I'm speaking from experience of inheriting a system that suffered from exactly this kind of problem.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 8

To add some more context to the trigger would help...

In your first database, use

DECLARE @ci VARBINARY(128) = CAST(DB_NAME() AS VARBINARY(128));

SET CONTEXT_INFO @ci;

And in the second, use:

IF CONVERT(VARCHAR(50),CONTEXT_INFO()) <> DB_NAME() AND trigger_nestlevel () > 1 RETURN;

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

ae_from_pa Old Hand Points: 346 More actions · Answer 9

How would the context setting be placed? For example, should it work if my first trigger (modify stmt) starts out like this:

======

ALTER TRIGGER [dbo].[UPDATE_EMAIL_IN_HEAT] ON [dbo].[CONTSUPP]

FOR DELETE

AS

BEGIN TRANSACTION

DECLARE @ci VARBINARY(128) = CAST(DB_NAME() AS VARBINARY(128));

SET CONTEXT_INFO @ci;

IF CONVERT(VARCHAR(50),CONTEXT_INFO()) <> DB_NAME() AND trigger_nestlevel () > 1 RETURN;

--Scenario 1

begin

update heat_test.gmdev.Contacts set emailID = null

where custid in

(select accountno from deleted where

deleted.contact = 'E-mail Address'

and deleted.mergecodes = 'HEAT1'

and deleted.linkacct is null)

and contacttype='Primary Contact'

and the second trigger in the other db (modify) starts like this:

ALTER TRIGGER [gmdev].[DELETE_EMAIL_IN_GOLDMINE2] ON [gmdev].[Contacts]

FOR DELETE

AS

BEGIN TRANSACTION

DECLARE @ci VARBINARY(128) = CAST(DB_NAME() AS VARBINARY(128));

SET CONTEXT_INFO @ci;

IF CONVERT(VARCHAR(50),CONTEXT_INFO()) <> DB_NAME() AND trigger_nestlevel () > 1 RETURN;

declare @custidexists varchar(100)

declare @contactidexists varchar(100)

set @contactidexists = null

set @contactidexists = (select top 1 contactid from deleted where custid is not null)

set @custidexists = null

set @custidexists = (select top 1 custid from deleted where custid is not null)

if @contactidexists is not null OR @custidexists is not null

BEGIN

???

Steve Jones - SSC Editor SSC Guru Points: 738093 More actions · Answer 10

Can I step back and ask why you need this? Not questioning you, but trying to help think a little outside the box about what might work.

My first thought is that you might want to add a column to the table that has a default value. If you are updating from another database, insert some marker in the column the trigger can read.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 11

ae_from_pa (2/3/2011)
How would the context setting be placed? For example, should it work if my first trigger (modify stmt) starts out like this:
======
ALTER TRIGGER [dbo].[UPDATE_EMAIL_IN_HEAT] ON [dbo].[CONTSUPP]
FOR DELETE
AS
BEGIN TRANSACTION
DECLARE @ci VARBINARY(128) = CAST(DB_NAME() AS VARBINARY(128));
SET CONTEXT_INFO @ci;
-- In this simple case you dont need this
-- if this trigger needs the functionality then it gets slightly more complex
-- let me know if two way checking is required...
--IF CONVERT(VARCHAR(50),CONTEXT_INFO()) <> DB_NAME() AND trigger_nestlevel () > 1 RETURN;
--Scenario 1
begin
update heat_test.gmdev.Contacts set emailID = null
where custid in
(select accountno from deleted where
deleted.contact = 'E-mail Address'
and deleted.mergecodes = 'HEAT1'
and deleted.linkacct is null)
and contacttype='Primary Contact'
and the second trigger in the other db (modify) starts like this:
ALTER TRIGGER [gmdev].[DELETE_EMAIL_IN_GOLDMINE2] ON [gmdev].[Contacts]
FOR DELETE
AS
BEGIN TRANSACTION
-- No, if you set the CONTEXT_INFO here you wipe out the one set in the first trigger : commented out
-- DECLARE @ci VARBINARY(128) = CAST(DB_NAME() AS VARBINARY(128));
-- SET CONTEXT_INFO @ci;
IF CONVERT(VARCHAR(50),CONTEXT_INFO()) <> DB_NAME() AND trigger_nestlevel () > 1 RETURN;
declare @custidexists varchar(100)
declare @contactidexists varchar(100)
set @contactidexists = null
set @contactidexists = (select top 1 contactid from deleted where custid is not null)
set @custidexists = null
set @custidexists = (select top 1 custid from deleted where custid is not null)
if @contactidexists is not null OR @custidexists is not null
BEGIN
???

I have amended your code (with a couple of comments). You just set CONTEXT_INFO in the primary trigger and read it in the secondary.

If you need two way checking i.e both triggers need to check whether to fire or not, then it becomes just a case of first checking if there is a value in CONTEXT_INFO() first.

Oh and get rid of those BEGIN TRANSACTION statements - they spell trouble...

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

mister.magoo SSC-Forever Points: 47174 More actions · Answer 12

OF course, if those are the real triggers, then are you not showing us the delete from the "other" database table in the first trigger?

Just checking because it won't work if don't set the CONTEXT_INFO in the correct place to start with...

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

ae_from_pa Old Hand Points: 346 More actions · Answer 13

Yes, two way checking is needed - deletes could start in either database, i want to have the second trigger not fire if the first one has already fired, otherwise it is trying to delete a record that was already deleted in the first database.

So...if I understand this right I need to reverse the statements...so the first trigger looks like

ALTER TRIGGER [dbo].[UPDATE_EMAIL_IN_HEAT] ON [dbo].[CONTSUPP]

FOR DELETE

AS

IF CONVERT(VARCHAR(50),CONTEXT_INFO()) <> DB_NAME() AND trigger_nestlevel () > 1 RETURN;

ELSE

DECLARE @ci VARBINARY(128) = CAST(DB_NAME() AS VARBINARY(128));

SET CONTEXT_INFO @ci;

--Scenario 1

begin

update heat_test.gmdev.Contacts set emailID = null

where custid in

(select accountno from deleted where

deleted.contact = 'E-mail Address'

and deleted.mergecodes = 'HEAT1'

and deleted.linkacct is null)

and contacttype='Primary Contact'

and the second one goes like this...???

ALTER TRIGGER [gmdev].[DELETE_EMAIL_IN_GOLDMINE2] ON [gmdev].[Contacts]

FOR DELETE

AS

IF CONVERT(VARCHAR(50),CONTEXT_INFO()) <> DB_NAME() AND trigger_nestlevel () > 1 RETURN;

DECLARE @ci VARBINARY(128) = CAST(DB_NAME() AS VARBINARY(128));

SET CONTEXT_INFO @ci;

declare @custidexists varchar(100)

declare @contactidexists varchar(100)

set @contactidexists = null

set @contactidexists = (select top 1 contactid from deleted where custid is not null)

set @custidexists = null

set @custidexists = (select top 1 custid from deleted where custid is not null)

if @contactidexists is not null OR @custidexists is not null

BEGIN

Would this work?

I have also cut out the BEGIN TRANSACTION in both triggers.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 14

Would it work? That all depends - if those were the only two triggers in use then yes - if you get rid of the ELSE - it is not needed.

Generally, if you want to prevent nested triggers from firing if the originating action( whether it be an insert, a delete or an update) was in another database then you need to check , set, and check again, like this :

CREATE TRIGGER db1_trigger1 ON db1.schema.table AFTER INSERT,DELETE,UPDATE

AS

DECLARE @ci VARBINARY(128) = CAST(DB_NAME() AS VARBINARY(128));

-- Check if anything has already set the CONTEXT_INFO and if not set it to my current database,

IF CONTEXT_INFO() IS NULL

SET CONTEXT_INFO @ci;

-- Check CONTEXT_INFO against my database and check the TRIGGER_NESTLEVEL

-- IF CONTEXT_INFO does not equal my database and TRIGGER_NESTLEVEL>1 just return

IF CONVERT(VARCHAR(50),CONTEXT_INFO()) <> DB_NAME() AND trigger_nestlevel () > 1 RETURN;

This code can be the same in all triggers that take part in these rules.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Trying to avoid recursive firing of delete triggers - please help!

Cookies on SQLServerCentral