SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Recursive Deletion using Triggers


Recursive Deletion using Triggers

Author
Message
richu.thomas
richu.thomas
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 113
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
Dave Morrison
Dave Morrison
SSC-Enthusiastic
SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)

Group: General Forum Members
Points: 171 Visits: 242
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?
richu.thomas
richu.thomas
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 113
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
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23631 Visits: 9730
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
tfifield
tfifield
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1297 Visits: 2890
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search