Recursive Inserts in trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Recursive Inserts in trigger

Author

Topic

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-11-06 : 12:23:30

Hiya,
I have a table called Immune (PatID,ImmuCode,ImmuDate)and a table ImmuneCodeDoubles (ImmuCode,Double1,Double2). Whenever a row is inserted into Immune whose ImmuCode corresponds to an ImmuCode in ImmuneCodeDoubles, it must be removed and 2 rows with the Double1 and Double2 codes inserted into Immune instead. I have tried doing this with an Insert trigger, but of course it causes recursive behavior. Recursive triggers are allowed, but it seems to exceed 32 nest levels.

Sarah Berger MCSD

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-11-06 : 12:28:58

Sounds more like an INSTEAD OF trigger than one ON INSERT ... Don't forget, you can always check the nest level in your trigger to determine if you should procede with a DML statement ...

Jay White
{0}

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-06 : 12:32:35

Are Double1 and Double2 allowed as ImmuCode in ImmuneCodeDoubles?
I suspect this is what is causing the problem.
If the problem is caused by ImmuCode being equal to Double1 or Double2 in the same record then just put a null there and change the trigger to not delete the original rather than dleete then insert

You can chenge the data in ImmuneCodeDoubles so that this never happens - this won't be possible without changing the structure as you need to be able to get more than 2 different entries added per entry in Immune.
The other way is to change the trigger to do all the inserts and deletes rather than rely on recursion.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-11-06 : 12:47:25

To Jay: Am using SQL 7, which doesn't support INSTEAD OF.
To Nigel: Double1 and Double2 cannot be the ImmuCode. Which kind of surprised me too why the recursion was happening, since I figured this would happen:
1. A row is inserted that needs replacement
2. Trigger fires, Inserts two rows
3. Trigger fires again, but no more inserts since there are no matches.

Seems there is a miscalculation here, or maybe it's because there's a SET NOCOUNT ON at the beginning of the trigger that's throwing it off.

The trigger looks like this:
CREATE TRIGGER trgImmunizDoubles ON [dbo].Immune
FOR INSERT
AS
SET NOCOUNT ON
INSERT INTO Immune SELECT PatID,D.DoubleCode,Immudate FROM Inserted INNER JOIN (SELECT ImmuCode,double1 AS DoubleCode FROM ImmuneCodeDoubles UNION SELECT Immucode,Double2 from ImmuneCodeDoubles)D on D.ImmuCode = Inserted.ImmuCode
DELETE Immune FROM Immune INNER JOIN ImmuneCodeDoubles D ON D.ImmuCode = Immune.ImmuCode
SET NOCOUNT OFF
RETURN

I think I will try to see if the @@NestLevel exceeds 2, and return.

Sarah Berger MCSD

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-11-06 : 12:51:04

Thanks, the trigger works fine now with @@Nestlevel.

Sarah Berger MCSD

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources