使用EF Core时SQL Server插入触发器未触发，仍抛出重复键异常

问题描述 #

我编写了一个用于处理重复键插入尝试的INSTEAD OF INSERT触发器：

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

直接在数据库管理系统中运行插入语句时，触发器可以正常工作，但使用EF Core（版本3.1.15）通过导航属性插入数据时，触发器似乎未生效，仍抛出重复键异常：

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

问题原因 #

1. 触发器未处理插入批次内的重复记录 #

当前触发器仅检查插入记录与表中已有数据的重复情况，但如果EF Core一次插入多条重复的关联记录（比如通过导航属性重复添加同一个关联），inserted表中会存在多条相同ProductId+CategoryId的记录。此时触发器的IF NOT EXISTS判断会认为没有与表中现有数据重复，从而执行INSERT操作，而插入多条重复记录会直接触发数据库的唯一键约束，导致报错。

2. 触发器的批量处理逻辑缺陷 #

当前触发器的逻辑是「只要插入批次中有任意一条记录与表中现有数据重复，就完全跳过所有插入」，而非仅跳过重复记录、插入不重复的记录。这种逻辑在EF Core的批量或多次插入场景下，会导致要么全插（可能包含批次内重复）要么全不插，无法正确处理部分重复的情况。

3. EF Core导航属性的插入行为 #

EF Core在处理导航属性关联时，可能会将关联关系的插入拆分为多条独立INSERT语句，或者在一次插入请求中包含重复的关联记录。当插入重复记录时，触发器的现有逻辑无法拦截批次内的重复，最终触发数据库的唯一键约束。

修正后的触发器示例 #

-- Create trigger to handle duplicate mapping attempts (including batch duplicates)
CREATE TRIGGER dbo.BlockDuplicates_Product_Category_Mapping
ON dbo.Product_Category_Mapping
INSTEAD OF INSERT
AS
BEGIN
  SET NOCOUNT ON;

  -- 插入不重复的记录：先对inserted去重，再排除表中已存在的
  INSERT INTO dbo.Product_Category_Mapping (ProductId, CategoryId, IsFeaturedProduct, DisplayOrder)
  SELECT DISTINCT i.ProductId, i.CategoryId, i.IsFeaturedProduct, i.DisplayOrder
  FROM inserted AS i
  WHERE NOT EXISTS (
      SELECT 1 
      FROM dbo.Product_Category_Mapping AS p
      WHERE i.ProductId = p.ProductId
        AND i.CategoryId = p.CategoryId
  );

  -- 可选：输出重复记录的提示
  IF EXISTS (
      SELECT 1
      FROM inserted AS i
      WHERE EXISTS (
          SELECT 1 
          FROM dbo.Product_Category_Mapping AS p
          WHERE i.ProductId = p.ProductId
            AND i.CategoryId = p.CategoryId
      )
      OR EXISTS (
          SELECT 1
          FROM inserted AS i2
          WHERE i2.ProductId = i.ProductId
            AND i2.CategoryId = i.CategoryId
          GROUP BY i2.ProductId, i2.CategoryId
          HAVING COUNT(*) > 1
      )
  )
  BEGIN
      PRINT 'Duplicate records detected and skipped';
  END
END

内容的提问来源于stack exchange，提问作者anastaciu