Trigger on cascading update target doesn't fire when the OUTPUT clause is used

Question

Hello. I asked this question on stackexchange and the guys from the community recommended me report it to Microsoft

https://dba.stackexchange.com/questions/346386/trigger-on-cascading-update-target-doesnt-fire-when-the-output-clause-is-used

Below is the copy-past from there.

I have 2 tables with the cascade delete rule - [dbo].[Invoices] and [dbo].[InvoiceRows]:

CREATE TABLE [dbo].[Invoices]
(
    [InvoiceId] [int] IDENTITY(1,1) NOT NULL,
    --other columns

    CONSTRAINT [PK_Invoices] 
        PRIMARY KEY CLUSTERED ([InvoiceId] ASC)
)
GO

CREATE TABLE [dbo].[InvoiceRows]
(
    [InvoiceRowId] [int] IDENTITY(1,1) NOT NULL,
    [ProductId] [int] NOT NULL,
    [Price] [money] NOT NULL,
    [Quantity] [int] NOT NULL,
    [InvoiceId] [int] NOT NULL
    --other columns

    CONSTRAINT [PK_InvoiceRows] 
        PRIMARY KEY CLUSTERED ([InvoiceRowId] ASC)
)

ALTER TABLE [dbo].[InvoiceRows] WITH CHECK 
    ADD CONSTRAINT [FK_InvoiceRows_Invoices] 
        FOREIGN KEY([InvoiceId]) REFERENCES [dbo].[Invoices] ([InvoiceId])
             ON UPDATE CASCADE ON DELETE CASCADE
GO

ALTER TABLE [dbo].[InvoiceRows] CHECK CONSTRAINT [FK_InvoiceRows_Invoices]
GO

I want any change in the [dbo].[InvoiceRows] to cause a recalculation of some register.

For this I added a trigger:

CREATE TRIGGER [dbo].[TrInvoiceRows_Delete_UpdateProductRegister]
ON [dbo].[InvoiceRows]
AFTER DELETE
AS 
BEGIN
    SET NOCOUNT ON;

    PRINT 'TRIGGER Tr_InvoiceRows_Delete_UpdateProductRegister fired'
    
    --trigger logic
END

All triggers fire correctly when I work directly with [dbo].[InvoiceRows]. The triggers work when I delete [dbo].[Invoices] using SSMS.

But recently I noticed that deleting [dbo].[Invoices] using Entity Framework generates the following code and the trigger IS NOT FIRED. That is, there are no any errors; it just ignores the trigger.

EXEC sp_executesql
    N'
    SET IMPLICIT_TRANSACTIONS OFF; 
    SET NOCOUNT ON; 
    DELETE FROM [Invoices]
    OUTPUT 1 
    WHERE [InvoiceId] = @p0; ',
    N'@p0 int',
    @p0=19936;

I noticed that the problem is OUTPUT 1 and compared the queries:

I realize that I have many options to fire a trigger on the application side (do not use EF or delete the cascade table rows first or set up the db context mapping .ToTable(tb => tb.UseSqlOutputClause(false))).

I want to know if it is possible to solve the problem on SQL Server ? That is, make the cascading table delete trigger always fire without any surprises.

Answer 1

If you change the DELETE statement to read

DELETE InvoiceRows OUTPUT 1 WHERE InvoiceId = 1

You get this error message:

Msg 334, Level 16, State 1, Line 51 The target table 'InvoiceRows' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

This may explain why the trigger does not fire in your original example. That is, there is a limitation with using OUTPUT in combination with a trigger. But you could certainly argue that you should an explicit error message also when deleting from Invoices. Silently ignoring the trigger is quite deceivable,