Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Recursive Deletion using Triggers Expand / Collapse
Author
Message
Posted Tuesday, January 4, 2011 9:25 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 4, 2012 3:20 AM
Points: 169, Visits: 111
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
Post #1042802
Posted Wednesday, January 5, 2011 5:15 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 3:48 AM
Points: 92, Visits: 201
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?




Post #1042954
Posted Wednesday, January 5, 2011 10:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 4, 2012 3:20 AM
Points: 169, Visits: 111
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
Post #1043220
Posted Wednesday, January 5, 2011 10:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
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
Post #1043227
Posted Thursday, January 6, 2011 1:14 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, September 28, 2014 1:41 PM
Points: 959, Visits: 2,888
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
Post #1044008
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse