SQL Server 2017创建支持多行插入的After Insert触发器

• 业务涉及两张数据表：
  • Product主表：Product_ID为主键，包含ProductName字段存储商品名称
  • ProductPublication业务表：Product_ID为外键关联Product主表，表内的ProductName是反范式设计的冗余字段，需要和主表数据保持一致
• 实现目标：在ProductPublication表上创建After Insert触发器，插入数据时自动从主表拉取正确的ProductName填充到冗余字段，触发器必须支持单次语句批量插入多行的场景
• 运行环境：SQL Server 2017

CREATE TABLE [Product] ( 
[Product_ID] [int] NOT NULL, 
[ProductName] [nvarchar] (20) NOT NULL,
PRIMARY KEY ([Product_ID]));

CREATE TABLE [ProductPublication] (
[ProductPublication_ID] [int] NOT NULL,
[Product_ID] [int] NOT NULL,
[ProductName] [nvarchar] (20) NOT NULL,
PRIMARY KEY ([ProductPublication_ID],[Product_ID]),
FOREIGN KEY ([Product_ID]) REFERENCES [Product] ([Product_ID]));

逐行单行插入数据时触发器可正常运行，测试语句如下：

INSERT [dbo].[Product] VALUES (1,N'Name1');
INSERT [dbo].[Product] VALUES (2,N'Name2');

INSERT [dbo].[ProductPublication] VALUES (1,1,'Name1');
INSERT [dbo].[ProductPublication] VALUES (1,2,'Name1');
INSERT [dbo].[ProductPublication] VALUES (2,1,'Name1');
INSERT [dbo].[ProductPublication] VALUES (2,2,'Name2');

DELETE FROM ProductPublication WHERE ProductPublication_ID in (1,2);

使用单次语句批量插入多行数据时，原有触发器执行失败，测试语句如下：

INSERT [dbo].[ProductPublication] VALUES (1,1,'Name1'), 
(1,2,'Name2'),(2,1,'Name3'),(2,2,'Name4');

最初编写的触发器仅支持单行插入场景，代码还存在关键字拼写错误（将TRIGGER写为TRIGGR），原始逻辑如下：

CREATE TRIGGER [dbo].[TR_ProductPublication_Insert] on [dbo].[ProductPublication]
AFTER INSERT AS 
SET NOCOUNT ON
BEGIN
   UPDATE prodpub 
   SET ProductName = (SELECT ProductName from Product
      WHERE Product_ID = (SELECT Product_ID from inserted))
   FROM ProductPublication prodpub 
   JOIN inserted i 
   ON prodpub.Product_ID = i.Product_ID;
END

错误原因：SQL Server触发器中inserted是存储本次插入所有行的临时表，当批量插入时inserted包含多行数据，子查询(SELECT Product_ID from inserted)会返回多个值，等值判断无法匹配导致报错，仅在单行插入时inserted只有一行数据才能正常执行。

修正逻辑后，通过关联当前更新行的Product_ID匹配主表商品名，仅更新本次插入的行，无论单次插入多少行都可正常执行，完整代码如下：

CREATE TRIGGER [dbo].[TR_ProductPublication_Insert] on [dbo].[ProductPublication]
AFTER INSERT AS 
SET NOCOUNT ON
BEGIN
   UPDATE prodpub 
   SET ProductName = (SELECT ProductName 
      from Product
      WHERE Product_ID = prodpub.Product_ID)
   FROM ProductPublication prodpub 
   JOIN inserted i 
   ON prodpub.ProductPublication_ID = i.ProductPublication_ID 
      AND prodpub.Product_ID = i.Product_ID;
END

逻辑说明：关联inserted表时使用联合主键ProductPublication_ID+Product_ID做匹配条件，避免同Product_ID的多行插入数据匹配错误；赋值时直接取当前更新行对应的主表商品名，不再依赖inserted的单值返回，可完美支持批量插入场景。

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