如何在SQL中新增采购记录时自动减少门店库存表对应游戏数量

嘿，我看你尝试用外键约束来自动更新店铺里的游戏库存，但这里有个关键误区——外键的ON UPDATE和ON DELETE子句只能用来定义级联操作（比如级联删除、置空这类），根本没办法直接写字段加减的逻辑哦。咱们得用触发器来实现这个需求，我给你一步步拆解怎么做：

问题分析 #

你的原SQL语句存在几个明显问题：

• 外键引用的表写错了（references TEHING ("GAMES ID")，应该是关联Game表的ID字段吧？）
• 外键的ON UPDATE/ON DELETE语法完全不符合规范，这些子句不支持直接赋值字段运算

正确解决方案（以PostgreSQL为例） #

我们需要为Purchase表的插入和删除事件分别创建触发器，来同步更新IN SHOP表的库存数量。

1. 创建插入采购记录时的触发器函数 #

这个函数会在新增采购记录后，找到对应店铺和游戏的库存记录并减1：

CREATE OR REPLACE FUNCTION update_stock_on_purchase_insert()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE "IN SHOP"
    SET "How many games in shop" = "How many games in shop" - 1
    WHERE "Shop ID" = NEW."Shop ID"
      AND "Game ID" = NEW."Game ID";
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 绑定插入触发器到Purchase表 #

让触发器在每次插入采购记录后执行上面的函数：

CREATE TRIGGER trigger_purchase_insert_update_stock
AFTER INSERT ON "Purchase"
FOR EACH ROW
EXECUTE FUNCTION update_stock_on_purchase_insert();

3. 创建删除采购记录时的触发器函数 #

这个函数会在删除采购记录后，把对应库存加1：

CREATE OR REPLACE FUNCTION update_stock_on_purchase_delete()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE "IN SHOP"
    SET "How many games in shop" = "How many games in shop" + 1
    WHERE "Shop ID" = OLD."Shop ID"
      AND "Game ID" = OLD."Game ID";
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

4. 绑定删除触发器到Purchase表 #

CREATE TRIGGER trigger_purchase_delete_update_stock
AFTER DELETE ON "Purchase"
FOR EACH ROW
EXECUTE FUNCTION update_stock_on_purchase_delete();

适配MySQL的写法 #

如果你的数据库是MySQL，触发器语法略有不同，不需要单独创建函数，直接在触发器里写逻辑：

-- 插入采购记录时更新库存
DELIMITER //
CREATE TRIGGER trigger_purchase_insert_update_stock
AFTER INSERT ON Purchase
FOR EACH ROW
BEGIN
    UPDATE `IN SHOP`
    SET `How many games in shop` = `How many games in shop` - 1
    WHERE `Shop ID` = NEW.`Shop ID`
      AND `Game ID` = NEW.`Game ID`;
END //
DELIMITER ;

-- 删除采购记录时更新库存
DELIMITER //
CREATE TRIGGER trigger_purchase_delete_update_stock
AFTER DELETE ON Purchase
FOR EACH ROW
BEGIN
    UPDATE `IN SHOP`
    SET `How many games in shop` = `How many games in shop` + 1
    WHERE `Shop ID` = OLD.`Shop ID`
      AND `Game ID` = OLD.`Game ID`;
END //
DELIMITER ;

额外注意事项 #

• 一定要确保IN SHOP表中每个(Shop ID, Game ID)组合是唯一的，否则UPDATE会意外修改多条记录。可以给IN SHOP表添加复合唯一约束：

  ALTER TABLE "IN SHOP" ADD CONSTRAINT unique_shop_game UNIQUE ("Shop ID", "Game ID");
  

• 如果库存不能为负数，建议在触发器函数里添加判断逻辑，避免出现负库存的情况。

内容的提问来源于stack exchange，提问作者John.Doh