INSTEAD OF DELETE 触发器与 ON DELETE CASCADE FK 冲突

Question

批次可以有多个账单，而账单又可以有多个账单行。我在它们之间有 ON DELETE CASCADE FK，这样如果您删除一个批次，相关的 Bill 和 BillLine 记录也会被删除。如果删除账单，关联的账单行将被删除，但批次记录不受影响。现在，如果一个或多个关联的 BillLine 记录存在特定数据条件，我需要防止删除 Bill。

表 Bill 显然需要 INSTEAD OF DELETE 触发器。 BillLine.BillId 有一个引用 Bill.BillId 的 ON DELETE CASCADE FK。我需要将 FK ON DELETE NO ACTION 设置为有意义，因为 INSTEAD OF DELETE 触发器有效地取代了 CASCADE 功能。当我删除账单时，INSTEAD OF DELETE 将删除关联的 BillLine 记录或根据某些数据条件引发异常。到目前为止，一切都很好。

但是，由于 Bill.BatchId 有一个引用 Batch.BatchId 的 ON DELETE CASCADE FK，SQL Server 不会让我创建触发器。这我不明白。为什么仅仅因为 Bill 上有一个触发器，我就必须在 Batch 上构建一个 INSTEAD OF DELETE 触发器？

下面创建表和键的代码（省略所有无关的列和键）就是现在的样子，没有 ON DELETE CASCADE 子句。问题是，为什么 FK_Bill_Batch_BatchId 不能有该子句，而不是我必须创建一个额外的 INSTEAD OF DELETE 触发器？

CREATE TABLE [Batch](
    [BatchId] [bigint] NOT NULL,
 CONSTRAINT [PK_Batch_BatchId] PRIMARY KEY CLUSTERED 
(
    [BatchId] ASC
)
)

CREATE TABLE [Bill](
    [BillId] [bigint] NOT NULL,
    [BatchId] [bigint] NOT NULL,
    [ReversesBillId] [bigint] NULL,
 CONSTRAINT [PK_Bill_BillId] PRIMARY KEY CLUSTERED 
(
    [BillId] ASC
)
)

ALTER TABLE [Bill]  WITH CHECK ADD  CONSTRAINT [FK_Bill_Batch_BatchId] FOREIGN KEY([BatchId])
REFERENCES [Batch] ([BatchId])

ALTER TABLE [Bill]  WITH NOCHECK ADD  CONSTRAINT [FK_Bill_ReversesBillId] FOREIGN KEY([ReversesBillId])
REFERENCES [Bill] ([BillId])

CREATE TABLE [BillLine](
    [BillLineId] [bigint] NOT NULL,
    [BillId] [bigint] NOT NULL,
    [ReversedByBillLineId] [bigint] NULL,
 CONSTRAINT [PK_BillLine_BillLineId] PRIMARY KEY CLUSTERED 
(
    [BillLineId] ASC
)
)

ALTER TABLE [BillLine]  WITH CHECK ADD  CONSTRAINT [FK_BillLine_Bill_BillId] FOREIGN KEY([BillId])
REFERENCES [Bill] ([BillId])

ALTER TABLE [BillLine]  WITH CHECK ADD  CONSTRAINT [FK_BillLine_ReversedByBillLineId] FOREIGN KEY([ReversedByBillLineId])
REFERENCES [BillLine] ([BillLineId])
GO

CREATE TRIGGER [Bill_Delete]
    ON [Bill]
    INSTEAD OF DELETE 
AS 
BEGIN
    SET NOCOUNT ON
    DECLARE @BillId UNIQUEIDENTIFIER

    DECLARE myCursor CURSOR LOCAL FORWARD_ONLY
        FOR SELECT    b.[BillId]
              FROM    deleted b
                      JOIN [Batch] bt on b.[BatchId] = bt.[BatchId]
    OPEN myCursor
    FETCH NEXT FROM myCursor INTO @BillId
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Delete BillLine records reversed by another BillLine in the same Bill
        DELETE FROM    [BillLine]
              WHERE    [BillId] = @BillId
                AND    [ReversedByBillLineId] IN
                       (SELECT bl.[BillLineId]
                          FROM [BillLine] bl
                         WHERE bl.BillId = @BillId
                       );

        -- Delete all remaining BillLine records for the Bill
        -- If the BillLine is reversed by a BillLine in a different Bill, the FK will raise an exception.
        -- That is the desired behavior.
        DELETE FROM    [BillLine]
              WHERE    [BillId] = @BillId;

        -- Delete the Bill
        DELETE FROM    [Bill]
              WHERE    [BillId] = @BillId;

        FETCH NEXT FROM myCursor INTO @BillId
    END
END
GO
CREATE TRIGGER [Batch_Delete]
    ON [Batch]
    INSTEAD OF DELETE 
AS 
BEGIN
    SET NOCOUNT ON
    DECLARE @BatchId UNIQUEIDENTIFIER

    DECLARE myCursor CURSOR LOCAL FORWARD_ONLY
        FOR SELECT    [BatchId]
              FROM    deleted
    OPEN myCursor
    FETCH NEXT FROM myCursor INTO @BatchId
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Delete all Bill records for the Batch.
        -- Another INSTEAD OF DELETE trigger on Bill will attempt to delete the associated BillLine records in the correct order.
        -- If the BillLine is reversed by a BillLine in a different Bill, FK_BillLine_ReversedByBillLineId will raise an exception.
        -- That is the desired behavior.
        DELETE FROM    [Bill]
              WHERE    [BatchId] = @BatchId;

        FETCH NEXT FROM myCursor INTO @BatchId
    END
END

如果您尝试用 ON DELETE CASCADE 替换 Batch_Delete 触发器：

DROP TRIGGER [Batch_Delete]
ALTER TABLE [Bill] DROP CONSTRAINT [FK_Bill_Batch_BatchId];
ALTER TABLE [Bill] WITH CHECK ADD CONSTRAINT [FK_Bill_Batch_BatchId] FOREIGN KEY([BatchId])
REFERENCES [Batch] ([BatchId]) ON DELETE CASCADE;

您会得到这样的结果：

Msg 1787, Level 16, State 0, Line 2
Cannot define foreign key constraint 'FK_Bill_Batch_BatchId' with cascaded DELETE or UPDATE on table 'Bill' because the table has an INSTEAD OF DELETE or UPDATE TRIGGER defined on it.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.

我不明白为什么这个方向上的 ON DELETE CASCADE 应该与 Bill 表上的 INSTEAD OF DELETE 触发器有关。

Answer 1

我知道这是一个老问题，但它值得一个答案：

当您的子表定义了 INSTEAD OF DELETE 触发器时，您无法指定 ON DELETE CASCADE 的原因是因为在触发器中，您可能决定不删除子表行，从而阻止级联生效。

由于不确定级联是否可能，数据库不知道如何处理这种情况，因此将问题留给开发人员来解决。

Answer 2

在我看来，你不需要 INSTEAD OF 触发器。
INSTEAD OF 触发器的工作始终代替操作。
您希望在某些情况下引发异常，并在其他情况下删除。

因此，您可以使用 DELETE CASCADE 和普通（AFTER）触发器。

在触发器内，您可以引发异常并可能回滚您的事务。
（触发器周围总是存在隐式事务）