Self-Reference cascading deletes

[cesar.guinovart]

I have the following table

CREATE TABLE [tbl_Items]
(
[item_id] int IDENTITY(1,1) CONSTRAINT PK_tbl_Items__item_id
PRIMARY KEY,
[parent_id] int DEFAULT(NULL) CONSTRAINT
FK_tbl_Items__item_id__parent_id REFERENCES [tbl_Items]( [item_id] ) ON
DELETE NO ACTION ON UPDATE NO ACTION
)
My Intention was to create a table that when I delete a record, all
records that have on the [parent_id] field the deleted record
[item_id].

I am trying to avoid having to use triggers or create a stored
procedure that firsts delete the children (recursively) and then
deletes the parent.

Is there any way to do this by changing my table definition here?

[Erland Sommarskog]

(ce*************@gmail.com) writes:

I have the following table

CREATE TABLE [tbl_Items]
(
[item_id] int IDENTITY(1,1) CONSTRAINT PK_tbl_Items__item_id
PRIMARY KEY,
[parent_id] int DEFAULT(NULL) CONSTRAINT
FK_tbl_Items__item_id__parent_id REFERENCES [tbl_Items]( [item_id] ) ON
DELETE NO ACTION ON UPDATE NO ACTION
)
My Intention was to create a table that when I delete a record, all
records that have on the [parent_id] field the deleted record
[item_id].

I am trying to avoid having to use triggers or create a stored
procedure that firsts delete the children (recursively) and then
deletes the parent.

Is there any way to do this by changing my table definition here?

While you can set up cascading foreign keys in some cases, this is not
one of them.

To use a an after trigger, you would have to remove the constraint.

But you could have an INSTEAD OF trigger. If you are using SQL 2000
you would have to first gather all children in a temp table, and
then delete all childre you found. In SQL 2005 you would use a
recursive CTE instead.

As you did not include which version of SQL Server you are using,
and the solutions are completely different, I don't include an example.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx