如何编写SQL触发器实现跨表列值相减并自动更新库存数量

需求为实现库存自动扣减逻辑：向Moving_list（调拨记录表）插入数据后，自动将Storage_Prod_List（库存表）中对应出库仓库、对应商品的库存值，减去本次调拨的数量，完成库存扣减。
现有触发器逻辑运行异常，无法得到预期结果。

-- 库存表
CREATE TABLE Storage_Prod_List
(
 STORAGE_PROD_ID     INT           PRIMARY KEY IDENTITY(1, 1),
 FK_SP_STORAGE_ID    INT,
 FK_SP_PRODUCT_ID    INT,
 SP_PRODUCT_QUANTITY    INT        NOT NULL
);

-- 调拨记录表 注意：原语句TO_SHOP字段末尾多余逗号，会导致建表失败
CREATE TABLE Moving_list
(
 MOVING_ID           INT           PRIMARY KEY IDENTITY(1, 1),
 MOVING_DATE         DATE         NOT NULL,
 MOVING_PRODUCT      INT,
 MOVING_QUANTITY     INT           NOT NULL,
 FROM_STORAGE        INT,
 TO_SHOP             INT,
);

-- 原有触发器
CREATE TRIGGER UpdateQuantity
ON Moving_list
AFTER INSERT
AS 
BEGIN
   UPDATE st
   SET SP_PRODUCT_QUANTITY = SP_PRODUCT_QUANTITY - (SELECT MOVING_QUANTITY FROM  Moving_list)
   FROM Storage_Prod_List st
   JOIN (SELECT MOVING_ID, SUM(MOVING_QUANTITY) AS Quantity FROM INSERTED GROUP BY   MOVING_ID) i ON st.STORAGE_PROD_ID = FK_SP_PRODUCT_ID
END;

1. 初始库存表数据：

2. 插入两条调拨记录：

3. 插入完成后期望库存数据：

• 关联逻辑错误：原JOIN条件用库存表自增主键STORAGE_PROD_ID关联商品ID，字段匹配完全错误，且没有关联出库仓库ID，无法定位到需要扣减的正确库存行。
• 扣减值获取逻辑错误：SET子句中查询全表Moving_list的MOVING_QUANTITY，批量插入时会触发“子查询返回多行”报错，单条插入时也会累加全表所有历史调拨数量做扣减，逻辑完全不符合需求。
• 未正确使用触发器内置临时表：AFTER INSERT触发器中INSERTED表会存储本次插入的所有新记录，不需要查询原表全量数据。
• 建表语法错误：Moving_list表最后一个字段TO_SHOP末尾多余逗号，会直接导致建表失败。

-- 修正后的调拨记录表建表语句，删除多余逗号
CREATE TABLE Moving_list
(
 MOVING_ID           INT           PRIMARY KEY IDENTITY(1, 1),
 MOVING_DATE         DATE         NOT NULL,
 MOVING_PRODUCT      INT,
 MOVING_QUANTITY     INT           NOT NULL,
 FROM_STORAGE        INT,
 TO_SHOP             INT
);
GO

-- 修正后的触发器
CREATE TRIGGER UpdateQuantity
ON Moving_list
AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;
    UPDATE st
    SET SP_PRODUCT_QUANTITY = st.SP_PRODUCT_QUANTITY - i.MOVING_QUANTITY
    FROM Storage_Prod_List st
    -- 按出库仓库、商品ID两个维度匹配对应库存记录
    INNER JOIN INSERTED i 
        ON st.FK_SP_STORAGE_ID = i.FROM_STORAGE 
        AND st.FK_SP_PRODUCT_ID = i.MOVING_PRODUCT
END;
GO

补充说明 #

• 修正后的触发器支持单次批量插入多条调拨记录，会自动逐行匹配对应库存完成扣减。
• 可根据业务需求扩展逻辑：比如扣减前判断库存是否充足，库存不足时抛出错误回滚事务，避免库存出现负数。

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