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).
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.
