MySQL中使用触发器实现现有列数值减法运算的方案咨询

前提说明 #

以下实现以更新product表的PROD_QOH字段扣减产品库存时，自动扣减part表零件库存为常见业务场景编写，你可以根据实际业务的触发时机、表关联规则自行调整逻辑。

原始表结构及初始化数据 #

create table product
(PROD_CODE varchar(100),
PROD_QOH int);

insert into product 
values("ABC", 1250);

create table part
(PART_CODE varchar(100),
PROD_QOH int);

insert into part
values("A", 567),
("B", 98),
('C', 549);

触发器编写示例 #

实现逻辑：当product表的PROD_QOH字段更新后的值小于旧值（即发生库存扣减）时，自动计算扣减差值，校验part表库存充足后执行批量扣减，库存不足时直接终止操作抛出错误。

DELIMITER //
CREATE TRIGGER trg_product_deduct_part_stock
AFTER UPDATE ON product
FOR EACH ROW
BEGIN
    -- 仅产品库存发生扣减时触发后续逻辑
    IF NEW.PROD_QOH < OLD.PROD_QOH THEN
        -- 计算需要扣减的库存数值
        SET @deduct_value = OLD.PROD_QOH - NEW.PROD_QOH;
        -- 校验所有零件库存是否足够扣减，避免出现负数
        IF EXISTS (SELECT 1 FROM part WHERE PROD_QOH < @deduct_value) THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '零件库存不足，无法完成扣减操作';
        END IF;
        -- 执行part表库存扣减，如有product和part的关联规则，可在此处增加WHERE条件筛选对应零件
        UPDATE part SET PROD_QOH = PROD_QOH - @deduct_value;
    END IF;
END //
DELIMITER ;

效果测试 #

执行以下语句扣减product库存10单位：

UPDATE product SET PROD_QOH = PROD_QOH - 10 WHERE PROD_CODE = 'ABC';

执行后查询part表，三个零件的PROD_QOH会分别更新为557、88、539；如果扣减数值超过任意零件的现有库存，操作会直接终止并抛出错误提示。

逻辑调整说明 #

• 如果product和part存在对应绑定关系，只需在UPDATE part的语句中增加WHERE条件筛选关联零件即可，不需要全表扣减
• 如果触发场景为插入/删除操作，只需将触发器的AFTER UPDATE改为AFTER INSERT/AFTER DELETE，调整扣减数值的取值逻辑即可

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