June 23, 2009 at 7:14 am
hello,
i am having a problem with a recursive delete with 2 Tables and parent child relations involved.
SQL Server Version is 2008.
Table1 is the master Table and holds some basic information. Table2 is connected over a foreign key and holds detail information. additionally Table2 has a self reference for parent/child relationship and another one for defining a source.
when i delete 1 or more entries from Table1 also all related entries from Table2 with the childs should be deleted.
i am using a instead of trigger on Table1 but as soon as i have a child in Table2 i am getting an foreign key error deleting the parent.
this is the Table structure:
Table1
tenant
instance
Table1Id
...
Table2
tenant
instance
Table1Id
Table2Id
ParentTable2Id
SourceTable2Id
...
the foreign keys are:
1.) Table1.Table1Id to Table2.Table1Id
2.) Table2.Table2Id to Table2.ParentTable2Id
3.) Table2.Table2Id to Table2.SourceTable2Id
this is the trigger that is giving me the error as soon as :
USE [Database]
GO
/****** Object: Trigger [Schema1].[TR_DeleteTable1] Script Date: 06/22/2009 17:51:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [Schema1].[TR_DeleteTable1]
ON [Schema1].[Table1]
INSTEAD OF DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
select * into #DeleteTable1
from deleted
BEGIN
delete ...
delete [Schema1].[Table2] from [Schema1].[Table2] Inner Join #DeleteTable1 On [Schema1].[Table2].[Table2Id] = #DeleteTable1.Table1Id;
delete [Schema1].[Table1] from [Schema1].[Table1] Inner Join #DeleteTable1 On [Schema1].[Table1].[Table1Id] = #DeleteTable1.Table1Id;
END
DROP TABLE #DeleteTable1
END
please help me on the delete for Table2.
thanks in advance,
Chris
June 23, 2009 at 12:57 pm
delete [Schema1].[Table2] from [Schema1].[Table2]
Inner Join Table2 P on Table2.Table2Id = P.ParentTable2Id
INNER JOIN #DeleteTable1 On [Schema1].[Table2].[Table2Id] = #DeleteTable1.Table1Id;
Something to this effect should do it...
June 24, 2009 at 7:06 am
Given foreign key "Table2.Table2Id to Table2.ParentTable2Id" and "Table2.Table2Id to Table2.SourceTable2Id" , then all of the "descendants" within both of these foreign keys must be deleted. The means to determime the descendants is by using a recursive common table element (CTE).
Create the tables and add some rows:
CREATE TABLE Table1
( table1Id INT NOT NULL
, CONSTRAINT Table1_PK PRIMARY KEY (table1Id)
)
CREATE TABLE Table2
( Table2Id INT NOT NULL
, Table1Id INT NOT NULL
, ParentTable2Id INT NOT NULL
, SourceTable2Id INT NOT NULL
, CONSTRAINT Table2_PK PRIMARY KEY (Table2Id)
, CONSTRAINT Table1_FK_Table2_Table1Id
FOREIGN KEY( Table1Id )
REFERENCES Table1( Table1Id )
ON DELETE NO ACTION
, CONSTRAINT Table2_FK_Table2_ParentTable2Id
FOREIGN KEY( ParentTable2Id )
REFERENCES Table2( Table2Id )
ON DELETE NO ACTION
, CONSTRAINT Table2_FK_Table2_SourceTable2Id
FOREIGN KEY( SourceTable2Id )
REFERENCES Table2( Table2Id )
ON DELETE NO ACTION
)
go
insert into Table1 ( Table1Id ) values (1) , (2) , (3)
--truncate table Table2
insert into Table2
( Table2Id, Table1Id , ParentTable2Id , SourceTable2Id )
VALUES(1 , 1 , 1, 1)
,(2 , 2 , 1, 1)
,(3 , 1 , 1, 1)
,(4 , 1 , 2, 1)
,(5 , 1 , 4, 3)
,(6 , 1 , 5, 3)
go
Create the trigger:
1) Trigger name is the concatenation of tableName , action , and when
2) Trigger names must be unique across all rows in sys.objects
CREATE TRIGGER Table1_tdi
ON Table1
INSTEAD OF DELETE
AS
SET NOCOUNT ON;
IF 0 = (SELECT COUNT(*) FROM deleted) RETURN
-- get all children under ParentTable2Id
;WITH Table2Children
( Table2Id, ParentTable2Id)
AS
(SELECTTable2.Table2Id
,Table2.ParentTable2Id
FROMdbo.Table2
JOINdeleted
ON deleted.Table1Id= Table2.Table1Id
UNION ALL
SELECTTable2.Table2Id
,Table2.ParentTable2Id
FROMdbo.Table2
JOINTable2Children
ON Table2.ParentTable2Id = Table2Children.Table2Id
WHERETable2.Table2Id Table2.ParentTable2Id
)
-- get all children under SourceTable2Id
, Table2Source
( Table2Id, ParentTable2Id)
AS
(SELECTTable2.Table2Id
,Table2.SourceTable2Id
FROMdbo.Table2
JOINdeleted
ON deleted.Table1Id= Table2.Table1Id
UNION ALL
SELECTTable2.Table2Id
,Table2.ParentTable2Id
FROMdbo.Table2
JOINTable2Children
ON Table2.ParentTable2Id = Table2Children.Table2Id
WHERETable2.Table2Id Table2.ParentTable2Id
)
DELETE FROM Table2
WHEREEXISTS
(SELECT 1
FROMTable2Children
WHERETable2Children.Table2Id = Table2.Table2Id
)
OREXISTS
(SELECT 1
FROMTable2Source
WHERETable2Source.Table2Id = Table2.Table2Id
)
;
DELETE FROM Table1
WHEREEXISTS
(SELECT 1
FROMdeleted
WHEREdeleted.Table1Id = Table1.Table1Id )
GO
Finally,run a test and confirm that the desired rows are deleted:
BEGIN TRAN
SELECT * FROMTable1
SELECT * FROMTable2
DELETE FROM Table1
WHERETable1Id = 2
SELECT * FROMTable1
SELECT * FROMTable2
ROLLBACK
SQL = Scarcely Qualifies as a Language
June 24, 2009 at 8:14 am
thank you very much, thats the solution
June 24, 2009 at 11:33 am
In a real RDMS, you could use cascade deletes but not in Sql Server 2008 (cyclic foreign key bug since sql 2000). Celko refers to Sql Server 2008 as "the lesser sql".
June 24, 2009 at 12:22 pm
Ahha - a colleague 😀
Just for fun, I created the same schema in SQLAnywhere but without the trigger and defined the foreign key constraints with the "on delete cascade" specification. SQL Anywhere deleted all of the appropriate rows with no problem.
P.S. Got a note from Joe last week about providing editing assistance with the next edition of SQL for Smarties.
SQL = Scarcely Qualifies as a Language
June 24, 2009 at 12:32 pm
The sad thing is that this feature (one of the most crucial features regarding referential integrity) has been requested way back in sql 2000. SQL Server is also missing subqueries in check constraints, no way to alter user defined types etc. Instead SQL Server 2008 introduces more proprietary crap like "Hierarchy Id" feature which actually PROMOTES procedural coding (to add a new node, you must get the last added node first etc).
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply