Recursive instead-of-delete triggers
-
I've just discovered that instead-of-delete triggers aren't recursive. That is, if you have an instead-of-delete trigger on a hierarchical table and you try to delete a record that has more than 1 level of children, it breaks.
Example: I have this structure:
A
-- B
---- D
---- E
-- C
When I delete A, an instead-of-delete trigger deletes all the rows whose idParent is A (B and C). I expected this to cascade on down and delete rows D and E also, like a cascading delete on a foreign key would. It doesn't. It just throws a foreign key violation. I grubbed in the SQL Server documentation and found that that is an acknowledged limitation.
My question is, how have you gotten around this ridiculous limitation? It makes instead-of-delete triggers useless. I can't use a cascading foreign key because of SQL Server's similar ridiculous inability to handle multiple and circular cascade paths.
Thanks,
K
quote:
-
I dont know that its a ridiculous limitation. Cascading deletes are there for precisely this case, to save writing a lot of extra code in the triggers. Once you decide that cascade doesn't work for you (why not?), then you have to write the code, much as you would have using SQL7 or earlier.
Andy
-
Luckily, you are not using version 6!
-
Had very similar problem. We have a workflow app where tasks can have several layers of child tasks. Child rows have a reference to the parent id. Had to write code (delete trigger) which created a temp table to collect all the ids for the child rows. Then scaned throught the temp table and deleted each one ending with the delete of the parent.
-
Andy -- I can't use cascade deletes because the parent points to a row in the same table; SQL Server considers this to be a circular reference and won't allow a cascading delete on the foreign key.
rprice -- Thanks for the confirmation -- I'll write something to delete them from the bottom up.
-
Will this help
If exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Parent_Child_Delete]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[Parent_Child_Delete]
GO
If exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Parent_Child]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Parent_Child]
GO
CREATE TABLE [dbo].[Parent_Child] ([Parent] [int] NOT NULL ,[Child] [int] NOT NULL)
GO
ALTER TABLE [dbo].[Parent_Child] WITH NOCHECK ADD CONSTRAINT [PK_Parent_Child] PRIMARY KEY CLUSTERED ([Parent],[Child])
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [Parent_Child_Delete] ON [dbo].[Parent_Child] INSTEAD of DELETE AS
Set NoCount On
Declare @aLevel Int,
@ToLoop TinyInt
Select @aLevel=0
Create Table #Tmp(Child Int,Current_Level Int)
Insert #Tmp
Select Child,@aLevel
From Deleted
If @@RowCount>0
Set @ToLoop=1
Else
Set @ToLoop=0
Delete from Parent_Child
From Deleted
Where Parent_Child.Parent=Deleted.Parent And
Parent_Child.Child=Deleted.Child
While @ToLoop>0
Begin
Insert #Tmp
Select p.Child,@aLevel+1
From #Tmp as t
Inner Join Parent_Child as p
On t.Child=p.Parent
If @@RowCount=0
Set @ToLoop=0
Delete From Parent_Child
From #Tmp
Where Parent_Child.Parent=#Tmp.Child And
#Tmp.Current_Level=@aLevel
Delete from #Tmp
Where Current_Level=@aLevel
Set @aLevel=@aLevel+1
-- If @aLevel>250 Print 'Circular of some sort or to many levels'
End
Set NoCount Off
Return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Insert [Parent_Child] values(1,2)
Insert [Parent_Child] values(1,3)
Insert [Parent_Child] values(2,20)
Insert [Parent_Child] values(2,25)
Insert [Parent_Child] values(3,30)
Insert [Parent_Child] values(3,35)
Insert [Parent_Child] values(3,38)
Insert [Parent_Child] values(35,300)
Insert [Parent_Child] values(300,3000)
GO
-- Test
Delete from Parent_Child Where Parent=35
Select * from Parent_Child
GO
Delete from Parent_Child Where Parent=2
Select * from Parent_Child
GO
-
Actually mine will be a little simpler. I have a trigger maintaining the lineage and depth of each node (http://www.sqlteam.com/item.asp?ItemID=8866) so that we don't ever have to do recursive queries. I can just stuff all the children into a temp table in one fell swoop.
-
The example can trace 'American family back to the Revolutionary War'. Quote from the article.
Done the lineage stuff in 6.5 but the lineage storage (varchar(255)) was a problem because of the key sizes used for the parent/children relationship.
With SQL 2000, 8000 characters one should be fairly safe.
Lastly, I am a big fan Joe Celko and his smarties. Thanks for the article.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply