January 4, 2011 at 9:25 pm
Hi,
I am having 2 tables namely Table1 and Table 2
Table 1
ID PK
Name
Desc
Table2
ChildID ->FK to ID in Table1
ParentID->FK to ID in Table1
All the main details shall be available in Table 1
and for each ID in Table 1 there may be Childrens in Table 2
These Childrens may again have Childrens
Table 1
0
1
2
Table2
ChildID ParentID
1 0
2 1
I want to delete an ID 0 from Table 1 and delete the Childrens(1) of ID 0 from table 2 ,subchildrens of children(1) that is 2 from table 2 and finally delete all the childrens details available in Table 1
I wrote a Instead of delete trigger on Table1 and Table2
Trigger on Table 1
Delete ChildID from Table 2 where ParentID in (Select ID from Deleted)
Delete ID from Table 1 where (Select ID from deleted)
Trigger on Table 2
delete dbo.Table2 where ChildID IN (select ChildID from deleted);
delete dbo.Table1 where ID in (select ChillID from deleted);
But i am getting a error as
Maximum stored procedure, function, trigger or view nesting level exceeded(32)
Could any one help me with this
January 5, 2011 at 5:15 am
Ricu, by the looks of it, you are getting this error because your two triggers are causing each other to fire, this is never a good idea as it'll just keep looping round until it hits this limit.
Is this a "one off" task you are wishing to do or is it something you are looking to maintain?
One quick and dirty approach would be to set the foreign keys to be cascading deletes, this would deal with it but may have undesired results.
Is there a maximum depth of parent child relationships?
If you can give a bit more detail around what you are trying to do I'm sure I can give some help on how to achieve this, for example do you have a set of id values you wish to delete (i.e. your 0,1,2,3 example) or are these rules you are wishing to apply?
January 5, 2011 at 10:51 am
Hi Dave,
Yeah i have a set if id's that is in the main Table1
eg)If i delete an ID 0 in the main Table 1 then the children 1 in Table2,the children of 1 in Table 2.... must be deleted from Table 2 and these ids 1 and 2 must also be deleted from Table1
January 5, 2011 at 10:56 am
First, why have the hierarchy in two tables? It's more usual to have one table and a parent ID that refers back to the same table.
Second, try having one trigger that crawls the whole hierarchy and deletes it all in one command. A recursive CTE that does a hierarchy crawl should allow this.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 6, 2011 at 1:14 pm
I would go with GSquared's solution.
However, if you want a quick and dirty way to avoid cross-firing triggers, stick this at the top of the triggers:
IF TRIGGER_NESTLEVEL() > 1
RETURN
This means that if the trigger is called due to another trigger in progress, the trigger nest level will be > 1 and the trigger will just exit.
The drawback on this is that if another trigger, not on either of the tables issues a valid DML statement that modifies these tables, the triggers on these tables won't fire as expected.
Todd Fifield
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply