SQL Server: Self-reference FK, trigger instead of ON DELETE CASCADE

Question

I need to perform an ON DELETE CASCADE on my table named CATEGORY, which has the following columls CAT_ID (BIGINT) NAME (VARCHAR) PARENT_CAT_ID (BIGINT)

PARENT_CAT_ID is a FK on CAT_ID. Obviously, the lovely SQL Server does not let me use ON DELETE CASCADE claiming circular or multiple paths to deletion.

A solution that I see often proposed is triggers. I made the following trigger:

USE [ma]
GO
/****** Object:  Trigger [dbo].[TRG_DELETE_CHILD_CATEGORIES]    Script Date: 11/23/2009 16:47:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TRG_DELETE_CHILD_CATEGORIES] ON [dbo].[CATEGORY] FOR DELETE AS
SET NOCOUNT ON
/* * CASCADE DELETES TO '[Tbl B]' */
DELETE CATEGORY FROM deleted, CATEGORY WHERE deleted.CAT_ID = CATEGORY.PARENT_CAT_ID

When I manually delete a category with child categories, I get the following exception:

Any idea what is wrong with my trigger?

UPDATE: Sorry for the edit, but I have another column CATEGORY.CAT_SCH_ID, which is a FK of another table CAT_SCH.ID. This FK has a CASCADE DELETE as well, meaning that once I delete a CAT_SCH, its CATEGORies must also be deleted. So, I get this error when I define the trigger:

Cannot create INSTEAD OF DELETE or INSTEAD OF UPDATE TRIGGER 'TRG_DEL_CATEGORY_WITH_CHILDREN' on table 'CATEGORY'. This is because the table has a FOREIGN KEY with cascading DELETE or UPDATE.

Any ideas?

Answer 1

The FOR DELETE trigger is raised after the original DELETE has been executed. To delete recursively, you need to write an INSTEAD OF DELETE trigger.

The algorithm is like this:

• Insert the PKs from deleted into a temp table

• Find detail records of records in temp table

• Loop until no more records are found

DELETE records in the table by joining with temp table.

I described recursive deletion in my blog.

Update

I guess you just need to drop that ON DELETE CASCADE flag from your recursive foreign key in Categories. The CASCADE flag on the foreign key from CAT_SCH should not matter.