SQL Server：自引用FK，而不是ON DELETE CASCADE [英] SQL Server: Self-reference FK, trigger instead of ON DELETE CASCADE

问题描述

我需要在名为CATEGORY的表上执行一个ON DELETE CASCADE，它有以下列表
CAT_ID（BIGINT）
NAME（VARCHAR）
PARENT_CAT_ID（BIGINT）

PARENT_CAT_ID是CAT_ID上的FK。显然，可爱的SQL Server不允许我使用ON DELETE CASCADE声明循环或多个路径删除。

我经常提出的解决方案是触发器。我做了以下的触发：

pre $ $ $

/对象：触发[dbo]。[TR​​G_DELETE_CHILD_CATEGORIES]脚本日期：11/23/2009 16:47:59 ****** /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

ALTER TRIGGER [dbo]。[TR​​G_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

当我手动删除一个带有子类别的类别时，我得到以下异常：

任何想法我的触发器有什么问题？

更新：
抱歉编辑，但我有另一列CATEGORY.CAT_SCH_ID，这是另一个表CAT_SCH.ID的FK。这个FK也有一个CASCADE DELETE，这意味着一旦我删除一个CAT_SCH，它的CATEGORIES也必须被删除。所以，当我定义触发器时，我得到这个错误：
$ b

无法在表'CATEGORY'上创建INSTEAD OF DELETE或INSTEAD UPDATE TRIGGER'TRG_DEL_CATEGORY_WITH_CHILDREN'。这是因为表有一个级联DELETE或UPDATE的FOREIGN KEY。

有什么想法吗？

在执行原始DELETE之后，引发FOR DELETE触发器。如果要递归删除，您需要编写一个INSTEAD OF DELETE触发器。

算法如下：

• 将已删除的PK插入临时表中




• 在临时表中查找记录的详细记录




• 循环直到找不到更多记录
  

删除记录这个表通过加入临时表。

我描述了递归删除在我的博客。
$ b 更新

我想你只需要从类别中的递归外键中删除ON DELETE CASCADE标志。 CAT_SCH的外键上的CASCADE标志应该没关系。

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?

解决方案

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.

这篇关于SQL Server：自引用FK，而不是ON DELETE CASCADE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！