MySQL 觸發器實戰：庫存扣減與 BEFORE/AFTER 選型避坑

場景：下單扣減庫存

CREATE TABLE goods(id INT PRIMARY KEY, name VARCHAR(20), num INT);
CREATE TABLE `order`(id INT AUTO_INCREMENT PRIMARY KEY, gid INT, much INT);

INSERT INTO goods VALUES (1, '電視機', 35), (2, '冰箱', 23);

錯誤示範：AFTER INSERT 無法阻止超賣

CREATE TRIGGER trigger1
AFTER INSERT ON `order`
FOR EACH ROW
UPDATE goods SET num = num - NEW.much WHERE id = NEW.gid;

-- 測試
INSERT INTO `order`(gid, much) VALUES (1, 5);  -- 庫存 35→30 ✓
INSERT INTO `order`(gid, much) VALUES (2, 100); -- 庫存 23→-77 ✗ 超賣！

原因：AFTER 觸發器在數據已寫入後執行，無法攔截、修改 NEW.much。

正確姿勢：BEFORE INSERT 校驗並修正

DROP TRIGGER trigger1;

CREATE TRIGGER trigger1
BEFORE INSERT ON `order`
FOR EACH ROW
BEGIN
    DECLARE stock INT;
    SELECT num INTO stock FROM goods WHERE id = NEW.gid;
    
    IF NEW.much > stock THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '庫存不足';
    END IF;
    
    -- 或自動修正為最大可買
    -- IF NEW.much > stock THEN SET NEW.much = stock; END IF;
    
    UPDATE goods SET num = num - NEW.much WHERE id = NEW.gid;
END;

BEFORE vs AFTER 選型原則

進階：多表級聯扣減（事務一致性）

CREATE TRIGGER trg_order_after
AFTER INSERT ON `order`
FOR EACH ROW
BEGIN
    -- 庫存扣減
    UPDATE goods SET num = num - NEW.much WHERE id = NEW.gid;
    
    -- 記錄出庫流水
    INSERT INTO stock_log(goods_id, qty, type) VALUES (NEW.gid, NEW.much, 'OUT');
    
    -- 更新銷售統計
    UPDATE daily_sales SET total = total + NEW.much WHERE goods_id = NEW.gid AND sale_date = CURDATE();
END;

注意：觸發器與觸發語句在同一事務，任一失敗全回滾，保證一致性。

避坑清單

• ❌ 觸發器中不能操作觸發表（UPDATE `order`...）→ 遞歸報錯
• ❌ 觸發器中不能調用 COMMIT/ROLLBACK、START TRANSACTION
• ❌ BEFORE 中修改 NEW 只對當前行生效，不影響其他行
• ✅ 使用 SIGNAL 拋出業務異常（MySQL 5.5+），應用層捕獲 SQLState 45000
• ✅ 複雜邏輯移至存儲過程，觸發器保持輕量