Hello,
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?