Posted by Erland Sommarskog on 08/31/06 21:17

(cesar.guinovart@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]