Self-Reference cascading deletes

Giganews Newsgroups
Subject: Self-Reference cascading deletes
Posted by:  cesar.guinova…@gmail.com
Date: 31 Aug 2006

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?

Replies