SQL Server:自引用 FK，触发器而不是 ON DELETE CASCADE

2022-01-01 00:00:00 sql foreign-keys sql-server triggers cascade

我需要在名为 CATEGORY 的表上执行 ON DELETE CASCADE，该表具有以下列CAT_ID (BIGINT)名称 (VARCHAR)PARENT_CAT_ID (BIGINT)

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 是 CAT_ID 上的 FK.显然，可爱的 SQL Server 不允许我使用 ON DELETE CASCADE 声明循环或多个删除路径.

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?

更新:抱歉编辑，但我有另一列 CATEGORY.CAT_SCH_ID，它是另一个表 CAT_SCH.ID 的 FK.这个 FK 也有一个 CASCADE DELETE，这意味着一旦我删除了一个 CAT_SCH，它的 CATEGORies 也必须被删除.因此，当我定义触发器时出现此错误:

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:

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

有什么想法吗?

推荐答案

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

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.

算法是这样的:

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

  • 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.

我在我的博客中描述了递归删除.

更新

我想您只需要从类别中的递归外键中删除 ON DELETE CASCADE 标志即可.来自 CAT_SCH 的外键上的 CASCADE 标志应该无关紧要.

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.

相关文章