SQL - 如何根据条件插入到表中？

Question

我有两个表 - 一个订单表和一个库存表。库存表跟踪 product_id、product_type 和剩余库存量。而订单表跟踪 order_id、product_id 和 order_amount。订单表通过 product_id 连接到库存表。

什么是最好的方法来确保我收到的订单绝不会出现订单金额 > 特定 product_id 的剩余库存？

我似乎无法将 INSERT INTO 与 WHERE 一起使用 - 其中我仅在订单金额 < 剩余库存金额时将订单添加到订单表。还有别的办法吗？我愿意改变我的餐 table 布置方式。

Answer 1

我会在订单表上添加一个触发器。该触发器将从剩余库存量中减去 order_amount。这将自动计算剩余库存。

此外，我会添加一个检查剩余库存值是否为 >= 0。这样您将阻止超出剩余库存的订单。插入将被回滚，因为它会违反库存检查。

---

CREATE FUNCTION update_inventory_amount()
  RETURNS trigger AS
$BODY$
DECLARE
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE
            inventory
        SET
            amount = amount - NEW.order_amount
        WHERE
            product_id = NEW.product_id;
    END IF;

    RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

CREATE TRIGGER update_inventory_amount_trigger
  BEFORE INSERT
  ON orders
  FOR EACH ROW
  EXECUTE PROCEDURE update_inventory_amount();

ALTER TABLE inventory ADD CONSTRAINT amount_check CHECK (amount >= 0);

以上触发代码仅用于UPDATE。只需稍加努力，您就可以将其扩展为还涵盖 UPDATE 和 DELETE。