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

Question

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

表 Bill 显然需要一个 INSTEAD OF DELETE 触发器。 BillLine.BillId 有一个 ON DELETE CASCADE FK 引用 Bill.BillId。我需要改为 FK ON DELETE NO ACTION 是有道理的，因为 INSTEAD OF DELETE 触发器有效地替换了 CASCADE 功能。当我删除 Bill 时，INSTEAD OF DELETE 将删除关联的 BillLine 记录或根据某些数据条件引发异常。到目前为止，还不错。

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

下面创建表和键的代码(省略了所有无关的列和键)是现在的样子，没有 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

如果您尝试将 Batch_Delete 触发器替换为 ON DELETE CASCADE:

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 的原因是因为在您的触发器中您可能决定不删除子表表行从而阻碍级联生效。

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