Recursive trigger
-
Hi,
I have a table with a simple Parent/Child self reference. Since SQL doesn't allow for cascade delete's on self referencing relationships, I decided to use a trigger.
I'd like this particular trigger to be recursive. Namely, when a Parent is deleted, all children (at all levels) is deleted. I'm not sure I'm comfortable setting recursive triggers on for the entire DB because of concerns about breaking something else. What's the next best option?
Thanks,
Joe
-
Create the work in a trigger calling a stored procedure recursively.
Just be careful, because if your hierarchy is of unknown depth, then you may encounter an error.
Sql server can only call nested items 32 levels deep. 1 trigger, and 1 stored procedure, your at 2 to start with, so if you have hierarchies more than 32 levels deep you will suffer an error.
Or build a temp object of all items to delete, and delete them all in 1 call, and not recursively.
-
Recursion in SQL Server 2000 is something to stay away from! Not only for the limitation in the nesting levels but for the stress this generates in the locking schema of the database.
If you can afford to perform DML only through stored procedures then your best bet is to use the temp table that contains all the affected items as pointed out above by Ray M
Don't use recursion.The other method is to try to map the Hierarchy to a "path" column and emit the delete statement using variations of "Like" in the path column.
Cheers,
* Noel -
Thanks for the replies.
Noel, by path, I assume you mean an un-normalized varchar column like the following:
ParentID1
ParentID1-ChildID1...
Now, I can do something like: delete ... where path like 'ParentID1%'
Right?
-
Correct!
* Noel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply