Sample Header Ad - 728x90

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

11 votes
2 answers
536 views
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
[![enter image description here](https://i.sstatic.net/2roWnuM6.png)](https://i.sstatic.net/2roWnuM6.png) 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: [![enter image description here](https://i.sstatic.net/Z4ib3OmS.png)](https://i.sstatic.net/Z4ib3OmS.png) 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). 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.
Asked by mrigrek74 (113 rep)
May 3, 2025, 04:05 PM
Last activity: May 6, 2025, 06:29 AM