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
[](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:
[](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
Last activity: May 6, 2025, 06:29 AM