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

I was able to get a working set of code with this entry:

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

FOR DELETE

AS

DECLARE @ci VARBINARY(128)

--next line had to be a separate statement,

--you cannot set a default value for a local variable at least in SQL 2K

SET @ci = 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 (SELECT CONTEXT_INFO

FROM master.dbo.SYSPROCESSES

WHERE SPID = @@SPID ) is null

--rewrote this with pointer to system table

UPDATE master.dbo.SYSPROCESSES

set context_info = @ci

where SPID = @@SPID

-- 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),(SELECT CONTEXT_INFO

FROM master.dbo.SYSPROCESSES

WHERE SPID = @@SPID ) ) <> DB_NAME() AND trigger_nestlevel () > 1 RETURN;

/*

declare @linkid varchar(20)

declare @recid varchar(15)*/

However when I try to compile i get the error

Msg 259, Level 16, State 2, Procedure UPDATE_EMAIL_IN_HEAT, Line 14

Ad hoc updates to system catalogs are not enabled. The system administrator must reconfigure SQL Server to allow this.

From what I read in http://msdn.microsoft.com/en-us/library/aa196704(SQL.80).aspx and other more recent posts, ad hoc updates of system tables are generally not a good idea. Is there a way to make this work without the ad hoc update?

Argh! No!

I said replace CONTEXT_INFO() with that, not CONTEXT_INFO. they are different!

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

FOR DELETE

AS

DECLARE @ci VARBINARY(128)

--next line had to be a separate statement,

--you cannot set a default value for a local variable at least in SQL 2K

SET @ci = 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 (SELECT CONTEXT_INFO

FROM master.dbo.SYSPROCESSES

WHERE SPID = @@SPID ) is null

--rewrote this with pointer to system table

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),(SELECT CONTEXT_INFO

FROM master.dbo.SYSPROCESSES

WHERE SPID = @@SPID ) ) <> DB_NAME() AND trigger_nestlevel () > 1 RETURN;

/*

declare @linkid varchar(20)

declare @recid varchar(15)*/

They are definitely not a good idea and you should not be doing it... I have amended the code above.

This compiles OK but I confess that I don't really understand what it is doing. I appreciate your help - I will try this out, probably over the weekend, and see how it goes. Thanks again!

Ok, let me try to explain.

CONTEXT_INFO is a place to store a value in your connected session specific to your SPID.

You can think of it like a note stuck on your computer screen.

You update a table in Database1 which has a trigger on it.

You look at the note on your screen (select context_info from sysprocesses where spid=@@spid) to see if you have already started work.

If the note is empty (null) you write on it "Database1" (SET CONTEXT_INFO @ci).

The trigger then tells you to switch to Database2 and delete a row from a table with another trigger on it.

After you delete the row in Database2 you look at the note on your screen (select context_info from sysprocesses where spid=@@spid) to see if you have already written on it.

You have! - it says "Database1", so you check that against your current database "Database2" and stop right there ( RETURN ) - ignoring any further code in the trigger in Database2.

I tried the new code but unfortunately it did not work. I got a message in my application that 'the record could not be modified because it had been changed by another user' . The delete did not complete in the second database. I did not get any SQL specific messages with more clues.

Would you suggest I run a trace?

One thing I am still unclear on: I can see that context_info is a local session variable - a note as you described. And I can see how you would set it to the db name if it was null (meaning work had not yet begun to populate it. But then in the third statement, aren't you going back to sysprocesses to retrieve the value of context_info from there? I don't see where the local variable gets compared to the db name.

Please excuse my ignorance - I feel I am getting closer to this but still not there. Any further suggestions are welcome. Thank you.

Ok, maybe CONTEXT_INFO is confusing the matter....

So, I have created a test script that creates two databases, two tables with two triggers and a third table to act as the control.

On a test system, run this script to see a working demo of the concept.

It drops everything right at the end, so if you want to examine the objects it creates, just remove the DROP DATABASE statements from the very end.

CREATE DATABASE [TrigTest1];

GO

USE [TrigTest1];

GO

/****** Object: Table [dbo].[testrig] Script Date: 02/07/2011 23:15:42 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[testrig1](

[id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,

[other] [varchar](10) NULL,

);

GO

SET ANSI_PADDING OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

--= This is the CONTEXT store.

CREATE TABLE [dbo].[TriggerContext] (spid int primary key not null,calling_db sysname not null);

GO

SET ANSI_PADDING OFF

GO

CREATE DATABASE [TrigTest2];

GO

USE [TrigTest2];

GO

/****** Object: Table [dbo].[testrig2] Script Date: 02/07/2011 23:15:42 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[testrig2](

[id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,

[other] [varchar](10) NULL,

);

GO

SET ANSI_PADDING OFF

GO

USE [TrigTest2];

GO

/****** Object: Trigger [dbo].[trig2] Script Date: 02/07/2011 23:06:34 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [dbo].[trig2] ON [dbo].[testrig2] AFTER DELETE AS

--= Check for an entry in the TriggerContext table for this SPID

DECLARE @calling_db sysname ;

SELECT @calling_db = calling_db FROM [TrigTest1].[dbo].[TriggerContext] WHERE spid=@@SPID ;

--= IF there was an entry and it is not for this database, just RETURN

IF @calling_db IS NOT NULL

AND DB_NAME()<>@calling_db RETURN ;

ELSE

--= If there was nothing in TriggerContext for this SPID, add something

INSERT [TrigTest1].[dbo].[TriggerContext](spid,calling_db) VALUES(@@SPID,db_name());

--= Perform normal trigger operations...here I perform a cross-database DELETE

DELETE FROM [TrigTest1].[dbo].[testrig1]

WHERE EXISTS(SELECT 1 FROM deleted WHERE deleted.id = [TrigTest1].[dbo].[testrig1].id) ;

--= Clean up the trigger context only where I created it.

IF (@calling_db IS NULL)

DELETE [TrigTest1].[dbo].[TriggerContext] WHERE spid=@@SPID ;

GO

USE [TrigTest1];

GO

/****** Object: Trigger [dbo].[trig1] Script Date: 02/07/2011 23:06:59 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [dbo].[trig1] ON [dbo].[testrig1] AFTER DELETE AS

--= Check for an entry in the TriggerContext table for this SPID

DECLARE @calling_db sysname ;

SELECT @calling_db = calling_db FROM [dbo].[TriggerContext] WHERE spid=@@SPID ;

--= IF there was an entry and it is not for this database, just RETURN

IF @calling_db IS NOT NULL

AND DB_NAME()<>@calling_db RETURN ;

ELSE

--= If there was nothing in TriggerContext for this SPID, add something

INSERT [dbo].[TriggerContext](spid,calling_db) VALUES(@@SPID,db_name());

--= Perform normal trigger operations...here I perform a cross-database DELETE

DELETE FROM [TrigTest2].[dbo].[testrig2]

WHERE EXISTS(SELECT 1 FROM deleted WHERE deleted.id = [TrigTest2].[dbo].[testrig2].id) ;

--= Clean up the trigger context only where I created it.

IF (@calling_db IS NULL)

DELETE [TrigTest1].[dbo].[TriggerContext] WHERE spid=@@SPID ;

GO

--= Create some test data

INSERT [TrigTest1].[dbo].[testrig1] (other) SELECT left(newid(),10) FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) row(source) ;

INSERT [TrigTest2].[dbo].[testrig2] (other) SELECT left(newid(),10) FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) row(source) ;

--= View the test data

SELECT id, other FROM [TrigTest1].[dbo].[testrig1] ORDER BY id ;

SELECT id, other FROM [TrigTest2].[dbo].[testrig2] ORDER BY id ;

GO

--= Test delete from database TrigTest1

DELETE [TrigTest1].[dbo].[testrig1] WHERE [id] = 2;

SELECT 'Delete ID 2 from database1',id,other from [TrigTest1].[dbo].[testrig1]

SELECT 'Trigger Deletes ID 2 from database2',id,other from [TrigTest2].[dbo].[testrig2]

GO

--= Test delete from database TrigTest2

DELETE [TrigTest2].[dbo].[testrig2] WHERE [id] = 4;

SELECT 'Delete ID 4 from database2',id,other from [TrigTest2].[dbo].[testrig2]

SELECT 'Trigger Deletes ID 4 from database1',id,other from [TrigTest1].[dbo].[testrig1]

GO

USE master;

GO

DROP DATABASE [TrigTest1];

GO

DROP DATABASE [TrigTest2];

Thanks - I have tried this and it seems to work exactly as intended!

Would you suggest I use the approach of creating the third table to act as the control? I had actually started down this road already and it would be fairly easy to rework the control table I have already created so it serves this purpose.

Yes, it is probably the best way to go.

Thanks again - much appreciated!