title: SQL Server: Self-reference FK, trigger instead of ON DELETE CASCADE
body: <p>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)</p>
  
  <p>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.</p>
  
  <p>A solution that I see often proposed is triggers. I made the following trigger:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>When I manually delete a category with child categories, I get the following exception:</p>
  
  <p><img src="https://dl.dropbox.com/u/1535349/screenshots/skata1.PNG" alt="exception"></p>
  
  <p>Any idea what is wrong with my trigger?</p>
  
  <p><strong>UPDATE:</strong>
  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:</p>
  
  <p><em>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.</em></p>
  
  <p>Any ideas?</p>