如何在MySQL中创建插入Sales表时更新Stock表的触发器？

很简单，我们可以创建一个AFTER INSERT触发器，在sales表成功插入新销售记录后，自动联动更新stock表中对应商品的库存数量。

触发器实现代码 #

DELIMITER //

CREATE TRIGGER update_stock_after_sale
AFTER INSERT ON sales
FOR EACH ROW
BEGIN
    -- 根据销售记录中的商品ID，扣减对应库存的数量
    UPDATE stock
    SET items_in_stock = items_in_stock - NEW.quantity
    WHERE stock_item_id = NEW.stock_item_id;
END //

DELIMITER ;

关键说明 #

• 使用AFTER INSERT：确保销售记录已经成功写入sales表，再执行库存更新，避免数据不一致。
• NEW关键字：代表刚刚插入到sales表中的那条新记录，通过NEW.stock_item_id获取销售的商品ID，NEW.quantity获取销售数量。
• 匹配条件：通过stock_item_id关联两张表，精准定位要更新的库存记录。

可选优化建议 #

1. 添加外键约束：为sales.stock_item_id添加外键关联stock.stock_item_id，防止插入不存在的商品ID导致库存更新无效：

   ALTER TABLE sales
   ADD CONSTRAINT fk_sales_stock_item
   FOREIGN KEY (stock_item_id) REFERENCES stock(stock_item_id);
   

2. 库存不足校验：如果需要防止库存负数，可以在触发器中添加判断，当库存不足时抛出错误：

   DELIMITER //
   
   CREATE TRIGGER update_stock_after_sale
   AFTER INSERT ON sales
   FOR EACH ROW
   BEGIN
       DECLARE current_stock INT;
       SELECT items_in_stock INTO current_stock FROM stock WHERE stock_item_id = NEW.stock_item_id;
       
       IF current_stock < NEW.quantity THEN
           SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '库存不足，无法完成销售';
       END IF;
       
       UPDATE stock
       SET items_in_stock = items_in_stock - NEW.quantity
       WHERE stock_item_id = NEW.stock_item_id;
   END //
   
   DELIMITER ;
   

测试的时候，你可以插入一条sales记录，然后查询stock表对应商品的items_in_stock，应该会看到数量已经减去了销售的quantity。

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