如何在新增buyer表desiredquantity时同步扣减stock表对应quantity

以下两种实现方式可以满足需求，按需选择即可：

方案1：数据库触发器实现（无需修改业务代码，自动触发） #

这是最推荐的方案，逻辑完全在数据库层执行，只要buyer表的desiredquantity字段发生更新就会自动触发库存扣减，以MySQL为例，触发器代码如下：

DELIMITER //
CREATE TRIGGER update_stock_after_buyer_update
AFTER UPDATE ON buyer
FOR EACH ROW
BEGIN
    -- 只在desiredquantity字段值增加时触发扣减
    IF NEW.desiredquantity > OLD.desiredquantity THEN
        -- 计算需要扣减的库存差值
        SET @deduct_num = NEW.desiredquantity - OLD.desiredquantity;
        -- 校验库存是否足够，不足则抛出错误中断操作
        IF (SELECT quantity FROM stock WHERE productID = NEW.productID) < @deduct_num THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '库存不足，无法更新';
        END IF;
        -- 扣减对应商品的库存
        UPDATE stock 
        SET quantity = quantity - @deduct_num 
        WHERE productID = NEW.productID;
    END IF;
    -- 如果需要支持desiredquantity减少时回加库存，补充以下逻辑即可
    IF NEW.desiredquantity < OLD.desiredquantity THEN
        SET @add_num = OLD.desiredquantity - NEW.desiredquantity;
        UPDATE stock 
        SET quantity = quantity + @add_num 
        WHERE productID = NEW.productID;
    END IF;
END //
DELIMITER ;

如果需要新增buyer记录时也触发库存扣减，额外新增一个INSERT类型的触发器即可，逻辑和上述一致，只需要去掉OLD字段的判断，直接用NEW.desiredquantity作为扣减值。

方案2：业务代码层实现（适合需要灵活扩展校验逻辑的场景） #

如果不想在数据库中维护触发器，也可以在业务代码中通过事务保证操作一致性，逻辑步骤如下：

• 开启数据库事务
• 查询当前要更新的buyer记录的旧desiredquantity值
• 计算需扣减的库存差值：delta = 新desiredquantity - 旧desiredquantity
• 查询对应productID的stock库存，校验quantity >= delta，不满足则直接返回错误
• 执行buyer表的desiredquantity更新语句
• 执行stock表的扣减语句：UPDATE stock SET quantity = quantity - ? WHERE productID = ?
• 所有操作执行成功后提交事务，任意步骤出错则回滚事务，保证数据一致性

注意事项 #

• 所有关联操作必须加事务，避免出现buyer更新成功但stock扣减失败的数据不一致问题
• 必须加库存不足校验，避免出现负数库存
• 如果业务场景支持超卖，可根据需求调整库存校验逻辑

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