INSTEAD OF DELETE Trigger conflict with ON DELETE CASCADE FK

Question

Batches can have multiple Bills which can have multiple BillLines. I have ON DELETE CASCADE FKs between them so that if you delete a Batch, the associated Bill and BillLine records also get deleted. If you delete a Bill, the associated BillLines get deleted but the Batch record is not affected. Now I need to prevent the delete of a Bill if there is a certain data condition with one or more of the associated BillLine records.

Table Bill clearly needs an INSTEAD OF DELETE trigger. BillLine.BillId has an ON DELETE CASCADE FK referencing Bill.BillId. It makes sense that I need to make that FK ON DELETE NO ACTION instead, because the INSTEAD OF DELETE trigger effectively replaces the CASCADE functionality. When I delete a Bill, the INSTEAD OF DELETE will either delete the associated BillLine records or raise an exception depending on certain data conditions. So far, so good.

However, because Bill.BatchId has an ON DELETE CASCADE FK referencing Batch.BatchId, SQL Server will not let me create the trigger. This I do not understand. Why should I have to build an INSTEAD OF DELETE trigger on Batch just because I have one on Bill?

The code to create the tables and keys below (with all extraneous columns and keys omitted) is how things are now, with no ON DELETE CASCADE clauses. The question is, why can't FK_Bill_Batch_BatchId have that clause instead of my having to create an additional INSTEAD OF DELETE trigger?

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

If you try to replace the Batch_Delete trigger with 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;

You'll get this:

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.

I don't understand why an ON DELETE CASCADE in this direction should have anything to do with the INSTEAD OF DELETE trigger on the Bill table.

Answer 1

I know this is an old question, but it deserves an answer:

The reason why you cannot specify ON DELETE CASCADE when your child table has an INSTEAD OF DELETE trigger defined is because in your trigger you may decide to not delete the child table rows thus impeding the cascade to take effect.

Since there is no certainty that a cascade is possible the database doesn't know how to handle that situation and thus leaves the issue to the developer to solve.