SQL触发器实现库存余额自动更新及Item主键设置咨询

1. 关于Item设为主键的可行性 #

完全可以把Item设为主键，但前提是每个商品的Item值唯一（比如用'Maize'代表玉米，'Millet'代表小米）。注意：如果用Item当主键，插入重复的Item值会直接报错，所以后续对已存在的商品操作得用UPDATE或者INSERT ... ON DUPLICATE KEY UPDATE，不能直接插重复Item。

先给你创建符合要求的表结构：

CREATE TABLE stock_details (
    Item VARCHAR(50) PRIMARY KEY,
    purchase_stock VARCHAR(10) NOT NULL CHECK (purchase_stock IN ('Purchase', 'Sale')),
    Quantity INT DEFAULT 0,
    Balance INT DEFAULT 0 -- 存储余额的字段
);

要是你需要记录多条交易流水，Item单独当主键就不合适了——得加个自增ID当主键，Item设成普通索引就行。但按你说的需求（维护每个商品的余额），Item做主键没问题。

2. 触发器实现余额自动变更 #

分两种场景给你写触发器，对应你提到的两个需求：

场景一：按初始需求（Purchase设固定余额，Sale扣减小米余额） #

先写BEFORE INSERT触发器，插入前自动设置余额：

DELIMITER //
CREATE TRIGGER trg_stock_balance_insert
BEFORE INSERT ON stock_details
FOR EACH ROW
BEGIN
    IF NEW.purchase_stock = 'Purchase' THEN
        CASE NEW.Item
            WHEN 'Maize' THEN SET NEW.Balance = 12;
            WHEN 'Millet' THEN SET NEW.Balance = 2;
        END CASE;
    END IF;
END //
DELIMITER ;

因为Item是主键，Sale操作不能用INSERT（会报错），所以得写BEFORE UPDATE触发器处理扣减逻辑：

DELIMITER //
CREATE TRIGGER trg_stock_balance_update
BEFORE UPDATE ON stock_details
FOR EACH ROW
BEGIN
    IF NEW.purchase_stock = 'Sale' AND NEW.Item = 'Millet' THEN
        SET NEW.Balance = OLD.Balance - NEW.Quantity;
    END IF;
END //
DELIMITER ;

场景二：Purchase时累加余额（实现玉米余额从12→23） #

如果你需要Purchase时在现有余额基础上加上Quantity（比如执行insert into stock_details ( Item, purchase_stock, Quantity) values("Maize","Purchase",11);后余额从12变23），那触发器逻辑要改成累加模式。同时因为Item是主键，得用INSERT ... ON DUPLICATE KEY UPDATE来执行操作，避免重复插入报错。

先写触发器：

DELIMITER //
CREATE TRIGGER trg_stock_balance_add
BEFORE INSERT ON stock_details
FOR EACH ROW
BEGIN
    IF NEW.purchase_stock = 'Purchase' THEN
        -- 先查询现有余额，没有的话默认给12（玉米初始值）
        SET @current_bal = (SELECT IFNULL(Balance, 12) FROM stock_details WHERE Item = NEW.Item);
        SET NEW.Balance = @current_bal + NEW.Quantity;
    ELSEIF NEW.purchase_stock = 'Sale' AND NEW.Item = 'Millet' THEN
        SET @current_bal = (SELECT Balance FROM stock_details WHERE Item = NEW.Item);
        SET NEW.Balance = @current_bal - NEW.Quantity;
    END IF;
END //
DELIMITER ;

然后执行你需要的插入语句，用ON DUPLICATE KEY UPDATE语法：

INSERT INTO stock_details (Item, purchase_stock, Quantity)
VALUES ("Maize","Purchase",11)
ON DUPLICATE KEY UPDATE
    purchase_stock = VALUES(purchase_stock),
    Quantity = VALUES(Quantity),
    Balance = Balance + VALUES(Quantity);

这条语句的作用是：如果Maize已经存在（余额12），就更新它的Quantity和Balance（12+11=23）；如果不存在，就插入一条新记录，Balance为默认12+11=23。

3. 测试示例 #

• 先插入玉米初始数据：

INSERT INTO stock_details (Item, purchase_stock, Quantity) VALUES ("Maize","Purchase",0);
-- 此时玉米Balance为12

• 执行你需要的插入操作：

INSERT INTO stock_details (Item, purchase_stock, Quantity) VALUES ("Maize","Purchase",11)
ON DUPLICATE KEY UPDATE
    purchase_stock = VALUES(purchase_stock),
    Quantity = VALUES(Quantity),
    Balance = Balance + VALUES(Quantity);
-- 玉米Balance变为23

• 小米Sale操作：

-- 先插入小米初始数据
INSERT INTO stock_details (Item, purchase_stock, Quantity) VALUES ("Millet","Purchase",0);
-- 执行Sale更新
UPDATE stock_details SET purchase_stock = 'Sale', Quantity = 1 WHERE Item = 'Millet';
-- 小米Balance从2变为1

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