AFTER DELETE trigger fires only if a DELETE is executed on a db query but not on EF Core

Question

I have two master-detail tables with a one-to-many relationship. I want to let a trigger fire after a deletion has happened, whether it has been as a cascade action (deleting the master by its PK) or by a specific action (deleting the detail by its PK).

Here's my trigger:

CREATE TRIGGER [dbo].[tr_update_stocks]
ON [dbo].[WarehouseComponentLoadRows]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON

    IF ((SELECT TRIGGER_NESTLEVEL()) > 1) 
       RETURN;

    DECLARE @Id BIGINT
    DECLARE @WarehouseId INT
    DECLARE @ComponentId INT
    DECLARE @ColorId INT
    DECLARE @Quantity DECIMAL (18,6)

    DECLARE deleted_cursor CURSOR FAST_FORWARD FOR
        SELECT 
            Id, WarehouseId, 
            ComponentId, ColorId,
            Quantity
        FROM 
            DELETED

    OPEN deleted_cursor
    FETCH NEXT FROM deleted_cursor into @Id, @WarehouseId, @ComponentId, @ColorId, @Quantity

    WHILE @@FETCH_STATUS = 0
    BEGIN
        UPDATE [dbo].[WarehouseComponents]
        SET [dbo].[WarehouseComponents].[StockQuantity] = [dbo].[WarehouseComponents].[StockQuantity] - @Quantity,
            [dbo].[WarehouseComponents].[AvailableQuantity] = [dbo].[WarehouseComponents].[AvailableQuantity] - @Quantity
        FROM
            [dbo].[WarehouseComponents]
        WHERE 
            [dbo].[WarehouseComponents].[WarehouseId] =  @WarehouseId 
            AND [dbo].[WarehouseComponents].[ComponentId] =  @ComponentId 
            AND [dbo].[WarehouseComponents].[ColorId] =  @ColorId

        FETCH NEXT FROM deleted_cursor INTO @Id, @WarehouseId, @ComponentId, @ColorId, @Quantity
  END

  CLOSE deleted_cursor
  DEALLOCATE deleted_cursor

  DECLARE inserted_cursor CURSOR FAST_FORWARD FOR
      SELECT Id
          , WarehouseId
          , ComponentId
          , ColorId
          , Quantity
      FROM INSERTED

  OPEN inserted_cursor
  FETCH NEXT FROM inserted_cursor into @Id, @WarehouseId, @ComponentId, @ColorId, @Quantity

  WHILE @@FETCH_STATUS = 0
  BEGIN

  UPDATE [dbo].[WarehouseComponents]
  SET
      [dbo].[WarehouseComponents].[StockQuantity] = [dbo].[WarehouseComponents].[StockQuantity] + @Quantity,
      [dbo].[WarehouseComponents].[AvailableQuantity] = [dbo].[WarehouseComponents].[AvailableQuantity] + @Quantity
  FROM
      [dbo].[WarehouseComponents]
  WHERE [dbo].[WarehouseComponents].[WarehouseId] =  @WarehouseId AND [dbo].[WarehouseComponents].[ComponentId] =  @ComponentId AND [dbo].[WarehouseComponents].[ColorId] =  @ColorId

  FETCH NEXT FROM inserted_cursor into @Id, @WarehouseId, @ComponentId, @ColorId, @Quantity
END

CLOSE inserted_cursor
DEALLOCATE inserted_cursor
END
GO

ALTER TABLE [dbo].[WarehouseComponentLoadRows] 
     ENABLE TRIGGER [tr_update_stocks]
GO

Here you find my master table:

CREATE TABLE [dbo].[WarehouseComponentLoads]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [SupplierId] [int] NOT NULL,
    [Date] [datetime2](7) NOT NULL,
    [CreatedAt] [datetime2](7) NULL,
    [CreatedBy] [nvarchar](255) NULL,
    [UpdatedAt] [datetime2](7) NULL,
    [UpdatedBy] [nvarchar](255) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[WarehouseComponentLoads] 
    ADD CONSTRAINT [PK_WarehouseComponentLoads] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
                      ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_WarehouseComponentLoads_SupplierId] 
ON [dbo].[WarehouseComponentLoads] ([SupplierId] ASC)
       WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
             SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
             ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

ALTER TABLE [dbo].[WarehouseComponentLoads] WITH CHECK 
    ADD CONSTRAINT [FK_WarehouseComponentLoads_Subjects_SupplierId] 
        FOREIGN KEY([SupplierId])
        REFERENCES [dbo].[Subjects] ([Id])
GO

ALTER TABLE [dbo].[WarehouseComponentLoads] CHECK CONSTRAINT [FK_WarehouseComponentLoads_Subjects_SupplierId]
GO

Here's my details table:

CREATE TABLE [dbo].[WarehouseComponentLoadRows]
(
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [WarehouseComponentLoadId] [int] NOT NULL,
    [WarehouseId] [int] NOT NULL,
    [ComponentId] [int] NOT NULL,
    [ColorId] [int] NOT NULL,
    [Quantity] [decimal](18, 6) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[WarehouseComponentLoadRows] 
    ADD CONSTRAINT [PK_WarehouseComponentLoadRows] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
                      ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_WarehouseComponentLoadRows_ColorId] 
ON [dbo].[WarehouseComponentLoadRows] ([ColorId] ASC)
       WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
             SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
             ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_WarehouseComponentLoadRows_ComponentId] 
ON [dbo].[WarehouseComponentLoadRows] ([ComponentId] ASC)
       WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
             SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
             ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_WarehouseComponentLoadRows_WarehouseComponentLoadId] 
ON [dbo].[WarehouseComponentLoadRows] ([WarehouseComponentLoadId] ASC)
       WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
             SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
             ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_WarehouseComponentLoadRows_WarehouseId] 
ON [dbo].[WarehouseComponentLoadRows] ([WarehouseId] ASC)
       WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
             SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
             ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

ALTER TABLE [dbo].[WarehouseComponentLoadRows]  WITH CHECK 
    ADD CONSTRAINT [FK_WarehouseComponentLoadRows_Colors_ColorId] 
        FOREIGN KEY([ColorId])
        REFERENCES [dbo].[Colors] ([Id])
GO

ALTER TABLE [dbo].[WarehouseComponentLoadRows] CHECK CONSTRAINT [FK_WarehouseComponentLoadRows_Colors_ColorId]
GO

ALTER TABLE [dbo].[WarehouseComponentLoadRows] WITH CHECK 
    ADD CONSTRAINT [FK_WarehouseComponentLoadRows_Components_ComponentId] 
        FOREIGN KEY([ComponentId])
        REFERENCES [dbo].[Components] ([Id])
GO

ALTER TABLE [dbo].[WarehouseComponentLoadRows] CHECK CONSTRAINT [FK_WarehouseComponentLoadRows_Components_ComponentId]
GO

ALTER TABLE [dbo].[WarehouseComponentLoadRows] WITH CHECK 
    ADD CONSTRAINT [FK_WarehouseComponentLoadRows_WarehouseComponentLoads_WarehouseComponentLoadId] 
        FOREIGN KEY([WarehouseComponentLoadId])
        REFERENCES [dbo].[WarehouseComponentLoads] ([Id])
            ON DELETE CASCADE
GO

ALTER TABLE [dbo].[WarehouseComponentLoadRows] CHECK CONSTRAINT [FK_WarehouseComponentLoadRows_WarehouseComponentLoads_WarehouseComponentLoadId]
GO

ALTER TABLE [dbo].[WarehouseComponentLoadRows] WITH CHECK 
    ADD CONSTRAINT [FK_WarehouseComponentLoadRows_Warehouses_WarehouseId] 
        FOREIGN KEY([WarehouseId])
        REFERENCES [dbo].[Warehouses] ([Id])
GO

ALTER TABLE [dbo].[WarehouseComponentLoadRows] CHECK CONSTRAINT [FK_WarehouseComponentLoadRows_Warehouses_WarehouseId]
GO

Here you find the stockings table to be updated:

CREATE TABLE [dbo].[WarehouseComponents]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [WarehouseId] [int] NOT NULL,
    [ComponentId] [int] NOT NULL,
    [ColorId] [int] NOT NULL,
    [StockQuantity] [decimal](18, 6) NOT NULL,
    [AvailableQuantity] [decimal](18, 6) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[WarehouseComponents] 
    ADD CONSTRAINT [PK_WarehouseComponents] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
                      ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_WarehouseComponents_ColorId] 
ON [dbo].[WarehouseComponents] ([ColorId] ASC)
       WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
             SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,  
             ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_WarehouseComponents_ComponentId] 
ON [dbo].[WarehouseComponents] ([ComponentId] ASC)
       WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
             SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
             ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_WarehouseComponents_WarehouseId] 
ON [dbo].[WarehouseComponents] ([WarehouseId] ASC)
       WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
             SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
             ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

ALTER TABLE [dbo].[WarehouseComponents] WITH CHECK 
    ADD CONSTRAINT [FK_WarehouseComponents_Colors_ColorId] 
        FOREIGN KEY([ColorId])
        REFERENCES [dbo].[Colors] ([Id])
GO

ALTER TABLE [dbo].[WarehouseComponents] CHECK CONSTRAINT [FK_WarehouseComponents_Colors_ColorId]
GO

ALTER TABLE [dbo].[WarehouseComponents] WITH CHECK 
    ADD CONSTRAINT [FK_WarehouseComponents_Components_ComponentId] 
        FOREIGN KEY([ComponentId])
        REFERENCES [dbo].[Components] ([Id])
GO

ALTER TABLE [dbo].[WarehouseComponents] CHECK CONSTRAINT [FK_WarehouseComponents_Components_ComponentId]
GO

ALTER TABLE [dbo].[WarehouseComponents] WITH CHECK 
    ADD CONSTRAINT [FK_WarehouseComponents_Warehouses_WarehouseId] 
        FOREIGN KEY([WarehouseId])
        REFERENCES [dbo].[Warehouses] ([Id])
GO

ALTER TABLE [dbo].[WarehouseComponents] CHECK CONSTRAINT [FK_WarehouseComponents_Warehouses_WarehouseId]
GO

Basically I am experiencing this issue: whenever I insert or update a entire master-detail entity using EF 7 everything works fine. Everything goes well also if I insert, update or delete a single row in the details table. But if I delete a master entity by its PK from the context and I save changes, it's like the trigger doesn't fire and while the master-detail records are correctly removed, the stockings aren't updated. This only happens from within the EF context. If I try to delete the master row by its PK from within SQL Server by a simple query, the cascade behavior is correctly applied and the trigger fires as expected, adjusting the stockings.

Please, consider that my fluent APIs for the my context contain a specific configuration to let EF know that I am using a trigger, as follows:

 modelBuilder.Entity<WarehouseComponentLoadRow>()
             .ToTable(tb => tb.HasTrigger("tr_update_stocks"));

Interestingly also, if I try to "manually" remove the children rows and then the parent rows within the same transaction scope (namely, before calling a SaveChangesAsync), the detail rows are deleted along with the master and even the stockings are updated as expected.

The problem only comes with Cascade deletion from the context. Is there something I am missing?

Answer 1

There is no way that EF Core is bypassing your triggers. There must be a bug in the trigger logic.

Your trigger as it stands may or may not be correct, but it uses a huge amount of Bad Stuff:

• Cursors, instead of a joined update.
• You would need to join inserted and deleted using a full-join, although in this case, because you want a qiantity difference, it could make more sense to use UNION ALL and GROUP BY.
• Use of three-part column names.
• Lack of table aliases to make it more readable.
• Checking TRIGGER_NESTLEVEL without passing an object ID.
• One would hope that (WarehouseId, ComponentId, ColorId) is a primary or unique key in both tables, otherwise you may end up updating multiple rows.

Your trigger should look like this:

CREATE OR ALTER TRIGGER dbo.tr_update_stocks
ON dbo.WarehouseComponentLoadRows
AFTER INSERT, UPDATE, DELETE
AS

IF @@ROWCOUNT = 0 OR TRIGGER_NESTLEVEL(@@PROCID) > 1
    RETURN;

SET NOCOUNT ON;


UPDATE wc
  SET
      StockQuantity     += i.DiffQuantity,
      AvailableQuantity += i.DiffQuantity
FROM
    dbo.WarehouseComponents wc
JOIN (
    SELECT
      i.WarehouseId,
      i.ComponentId,
      i.ColorId,
      SUM(i.Quantity) AS DiffQuantity
    FROM (
        SELECT
          i.WarehouseId,
          i.ComponentId,
          i.ColorId,
          i.Quantity
        FROM inserted i

        UNION ALL

        SELECT
          d.WarehouseId,
          d.ComponentId,
          d.ColorId,
          -d.Quantity
        FROM deleted d
    ) i
    GROUP BY
      i.WarehouseId,
      i.ComponentId,
      i.ColorId
) i ON i.WarehouseId = wc.WarehouseId
   AND i.ComponentId = wc.ComponentId
   AND i.ColorId = wc.ColorId;

---

To be honest, if you all you want is the total child quantity then you should probably just use a view instead of triggers.

CREATE VIEW dbo.TotalWarehouseComponentLoadRows
WITH SCHEMABINDING
AS

SELECT
  wcl.WarehouseId,
  wcl.ComponentId,
  wcl.ColorId,
  SUM(wcl.Quantity) AS Quantity,
  COUNT_BIG(*) AS Count
FROM dbo.WarehouseComponentLoadRows wcl
GROUP BY
  wcl.WarehouseId,
  wcl.ComponentId,
  wcl.ColorId;

For extra performance, you could make that into an indexed view.

CREATE UNIQUE CLUSTERED INDEX IX ON WarehouseComponentLoadRows
  (WarehouseId, ComponentId, ColorId);