SQL Server中Purchases多插入时同步Stocks表的触发器实现问题

表结构 #

CREATE TABLE Stocks
(
  id int PRIMARY KEY IDENTITY(1,1),
  itemId int NOT NULL,
  qty int  NOT NULL,
  status NVarChar(50) NOT NULL,
)

CREATE TABLE Purchases
(
  id int PRIMARY KEY IDENTITY(1,1),
  itemId int NOT NULL,
  suppId int NOT NULL,
  qty int  NOT NULL,
  Date NVarChar(50) NOT NULL,
  status NVarChar(50) NOT NULL,
)

需求 #

向Purchases表插入多条记录时，自动同步Stocks表：

• 若Stocks中已存在对应itemId，则更新该记录的qty（累加采购数量）
• 若不存在，则向Stocks插入新记录，status设为'available'

现有问题 #

原触发器仅能处理单条插入，批量插入时仅会处理第一条记录，无法同步其余记录。原因是原代码使用变量获取INSERTED表的单条数据，而非基于集合的操作。

解决方案 #

使用SQL Server的MERGE语句实现基于集合的同步操作，一次性处理所有插入的采购记录：

CREATE TRIGGER [dbo].[trPurchaseInsert] ON [dbo].[Purchases]
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    -- 按itemId汇总采购数量，避免同一itemId多条插入重复处理
    WITH PurchaseSummary AS (
        SELECT itemId, SUM(qty) AS totalQty
        FROM INSERTED
        GROUP BY itemId
    )
    MERGE INTO Stocks AS s
    USING PurchaseSummary AS p
        ON s.itemId = p.itemId
    WHEN MATCHED THEN
        UPDATE SET s.qty = s.qty + p.totalQty
    WHEN NOT MATCHED THEN
        INSERT (itemId, qty, status)
        VALUES (p.itemId, p.totalQty, 'available');
END

代码说明 #

1. 汇总采购数量：通过CTE先按itemId汇总采购总量，避免同一商品多条采购记录重复触发更新
2. MERGE操作：
   • 匹配Stocks和汇总后的采购记录的itemId
   • 匹配时：累加库存数量
   • 不匹配时：插入新库存记录，id由IDENTITY自动生成，status固定为available

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