SQL Server insert trigger not firing using EF Core

Question

I have this insert trigger which handles duplicate key insert attempts:

-- Create trigger to not throw in case of duplicate mapping attempt
CREATE TRIGGER dbo.BlockDuplicates_Product_Category_Mapping
ON dbo.Product_Category_Mapping
INSTEAD OF INSERT
AS
BEGIN
  SET NOCOUNT ON;

  IF NOT EXISTS (SELECT 1 
                 FROM inserted AS i 
                 INNER JOIN dbo.Product_Category_Mapping AS p
                            ON i.ProductId = p.ProductId
                            AND i.CategoryId = p.CategoryId)
  BEGIN
      INSERT INTO dbo.Product_Category_Mapping (ProductId, CategoryId, IsFeaturedProduct, DisplayOrder)
          SELECT ProductId, CategoryId, IsFeaturedProduct, DisplayOrder 
          FROM inserted;
  END
  ELSE
  BEGIN
      PRINT 'Duplicate';
  END
END

And it works fine if I run it directly from my DBMS:

But, if I use it in code with EF:

<PackageReference Include="Microsoft.EntityFrameworkCore" Version="3.1.15" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="3.1.15" />
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="3.1.15" />

It doesn't seem to work, a duplicate key exception is thrown, which is what I was trying to avoid:

Microsoft.EntityFrameworkCore.Relational: An error occurred while updating the entries. See the inner exception for details. Core Microsoft SqlClient Data Provider: Violation of UNIQUE KEY constraint 'UQ_PCM_ProductId_CategoryId'. Cannot insert duplicate key in object 'dbo.Product_Category_Mapping'. The duplicate key value is (920, 129).

This is being inserted through a navigation property, if it makes any difference.

Can someone point out to me why this is not working?

I followed these two related questions but I don't see nothing that can help:

SQL Server 2008 insert trigger not firing

SQL Server 2008 - insert trigger not firing

Answer 1

I'm going to bang the drum again: This idea is a bad idea. Giving the impression to an application/user that things "worked" when they didn't is not a good idea. In this case you say it's "not a problem" but I would say with confidence that at some point a user is going to ask "Why is the product I added just now not featured, when I asked it to be." and the answer will be "Your INSERT was ignored, but we smothered that error for you and didn't tell you."

That being said, you're going about this the wrong way if you "must" (don't) do this in a trigger. If you're inserting multiple rows this is going to go all sorts of wrong, as if even 1 of those rows is a duplicate you ignore all of them. Inserting 100 new products, and 1 is a dupe? You insert zero.

Instead, use a INSERT with a NOT EXISTS and "bin" the rest. Not ideal, and your users will be confused, but this appears to be the behaviour you are after, at least:

CREATE TRIGGER dbo.BlockDuplicates_Product_Category_Mapping
ON dbo.Product_Category_Mapping
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.Product_Category_Mapping (ProductId,
                                              CategoryId,
                                              IsFeaturedProduct,
                                              DisplayOrder)
    SELECT i.ProductId,
           i.CategoryId,
           i.IsFeaturedProduct,
           i.DisplayOrder
    FROM inserted i
    WHERE NOT EXISTS (SELECT 1
                      FROM dbo.Product_Category_Mapping PCM
                      WHERE PCM.ProductId = i.ProductId
                        AND PCM.CategoryId = i.CategoryId);
END;

If you really wanted to be nice to your users (to avoid their confusion when things aren't inserted), you could do an Upsert. Then you UPDATE the rows that already existed, to the values they wanted to INSERT, and INSERT the new ones.

CREATE TRIGGER dbo.BlockDuplicates_Product_Category_Mapping
ON dbo.Product_Category_Mapping
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    --No need to define a transaction, as a trigger is already inside one and automatically has XACT_ABORT enabled

    UPDATE PCM
    SET IsFeaturedProduct = i.IsFeaturedProduct,
        DisplayOrder = i.DisplayOrder
    FROM dbo.Product_Category_Mapping PCM WITH (UPDLOCK, SERIALIZABLE)
        JOIN inserted i ON PCM.ProductId = i.ProductId
                       AND PCM.CategoryId = i.CategoryId;

    INSERT INTO dbo.Product_Category_Mapping (ProductId,
                                              CategoryId,
                                              IsFeaturedProduct,
                                              DisplayOrder)
    SELECT i.ProductId,
           i.CategoryId,
           i.IsFeaturedProduct,
           i.DisplayOrder
    FROM inserted i
    WHERE NOT EXISTS (SELECT 1
                      FROM dbo.Product_Category_Mapping PCM
                      WHERE PCM.ProductId = i.ProductId
                        AND PCM.CategoryId = i.CategoryId);
END;