SQL Server: FK self-regulation, trigger instead of ON DELETE CASCADE

Question

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

PARENT_CAT_ID is the FK on CAT_ID. Obviously, the excellent SQL Server does not allow the use of ON DELETE CASCADE, requiring a circular or multiple path for deletion.

The solution, which, as I see it, is often proposed, is a trigger. 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 editing, but I have another column CATEGORY.CAT_SCH_ID, which is the FK of another table CAT_SCH.ID. This FK also has a CASCADE DELETE, which means that after CAT_SCH is deleted, its CATEGORIES must also be deleted. So, I get this error when I define a trigger:

Unable to create INSTEAD OF DELETE or INSTEAD OF UPDATE TRIGGER 'TRG_DEL_CATEGORY_WITH_CHILDREN' in the 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 initial DELETE is executed. To remove recursively, you need to write an INSTEAD OF DELETE trigger.

The algorithm is as follows:

• Insert PK from remote to temporary table

• Find record entries in temp table

• Loop until more records are found

DELETE records in the table by connecting to the temporary table.

I described recursive deletion on my blog .

Update

I think you just need to drop the ON DELETE CASCADE flag from your recursive foreign key in categories. The CASCADE flag of the foreign key from CAT_SCH must not have a value.