SQLite中实现商品库存扣减及销售自动更新触发器的方法

要将productTB中两条'A'记录的QTY从1、4更新为0、3，可以用直观的分步更新方式，确保优先扣减库存较少的记录：

-- 先扣减第一条库存>0的'A'记录，将其QTY从1变为0
UPDATE productTB
SET QTY = QTY - 1
WHERE NAME = 'A' AND QTY > 0
ORDER BY QTY ASC
LIMIT 1;

-- 再扣减剩余1个库存，将第二条记录的QTY从4变为3
UPDATE productTB
SET QTY = QTY - 1
WHERE NAME = 'A' AND QTY > 0
ORDER BY QTY ASC
LIMIT 1;

如果偏好单语句实现，也可以用窗口函数计算累计扣减量：

WITH ranked_products AS (
  SELECT 
    NAME,
    QTY,
    SUM(QTY) OVER (PARTITION BY NAME ORDER BY QTY ASC) AS cum_qty
  FROM productTB
  WHERE NAME = 'A'
)
UPDATE productTB
SET QTY = CASE
  WHEN cum_qty <= 2 THEN 0
  ELSE QTY - (2 - (cum_qty - QTY))
END
FROM ranked_products
WHERE productTB.NAME = ranked_products.NAME 
  AND productTB.QTY = ranked_products.QTY;

首先需要一个存储销售记录的表（假设为salesTB），然后通过触发器实现库存自动扣减：

步骤1：创建销售表 #

CREATE TABLE IF NOT EXISTS salesTB (
  ID INTEGER PRIMARY KEY AUTOINCREMENT,
  NAME TEXT NOT NULL,
  SALE_QTY INTEGER NOT NULL CHECK(SALE_QTY > 0)
);

步骤2：开启递归触发器支持 #

SQLite触发器的循环逻辑需要开启递归触发器：

PRAGMA recursive_triggers = ON;

步骤3：创建触发器 #

CREATE TRIGGER update_product_stock_after_sale
AFTER INSERT ON salesTB
FOR EACH ROW
BEGIN
  DECLARE remaining_qty INTEGER;
  SET remaining_qty = NEW.SALE_QTY;
  
  -- 循环扣减库存，直到销售数量全部扣完或无库存可扣
  WHILE remaining_qty > 0 DO
    UPDATE productTB
    SET QTY = QTY - 1
    WHERE NAME = NEW.NAME AND QTY > 0
    ORDER BY QTY ASC
    LIMIT 1;
    
    -- 无库存可扣时退出循环，避免死循环
    IF (SELECT changes() = 0) THEN
      LEAVE;
    END IF;
    
    SET remaining_qty = remaining_qty - 1;
  END WHILE;
END;

测试触发器 #

插入销售记录后，触发器会自动更新库存：

-- 插入2个'A'的销售记录，库存将自动从1、4变为0、3
INSERT INTO salesTB (NAME, SALE_QTY) VALUES ('A', 2);

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