MySQL 觸發器入門與實戰：INSERT/UPDATE/DELETE 同步維護庫存

什麼是觸發器？

觸發器是與表關聯的資料庫對象，監聽特定事件 (INSERT/UPDATE/DELETE) 在 BEFORE 或 AFTER 觸發，自動執行預定義 SQL。常用於：審計日誌、衍生欄維護、跨表同步、資料驗證。

語法結構

CREATE TRIGGER trigger_name
  trigger_time trigger_event
  ON table_name
  FOR EACH ROW
  trigger_body

• trigger_time：BEFORE | AFTER
• trigger_event：INSERT | UPDATE | DELETE
• FOR EACH ROW：行級觸發 (MySQL 僅支援行級)
• NEW：新行數據 (INSERT/UPDATE 可用)
• OLD：舊行數據 (UPDATE/DELETE 可用)

關鍵字衝突處理

ORDER 是保留字，作為表名需用反引號包裹：\`order\`。

實戰案例：下單自動扣減庫存

1. 建表

CREATE TABLE goods (
  goods_id INT PRIMARY KEY AUTO_INCREMENT,
  goods_name VARCHAR(64),
  shop_price DECIMAL(10,2),
  goods_number INT
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `order` (
  order_id INT PRIMARY KEY AUTO_INCREMENT,
  goods_id INT,
  goods_name VARCHAR(64),
  buy_number INT
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO goods VALUES
  (NULL, 'nokiaN85', 2000, 35),
  (NULL, 'iphone4S', 4500, 30),
  (NULL, 'Lumia', 5000, 40),
  (NULL, 'samsung', 4200, 20);

2. AFTER INSERT：下單扣庫存

-- 固定扣減 5 (示範基本語法)
CREATE TRIGGER alter_goods_number
AFTER INSERT ON `order`
FOR EACH ROW
UPDATE goods SET goods_number = goods_number - 5 WHERE goods_id = 1;

-- 實戰：根據訂單動態扣減 (使用 NEW)
CREATE TRIGGER alter_goods_number
AFTER INSERT ON `order`
FOR EACH ROW
UPDATE goods
SET goods_number = goods_number - NEW.buy_number
WHERE goods_id = NEW.goods_id;

測試：INSERT INTO \`order\` VALUES (NULL, 4, 'samsung', 5); → samsung 庫存 20→15。

3. AFTER DELETE：取消訂單回補庫存 (使用 OLD)

CREATE TRIGGER back_goods_number
AFTER DELETE ON `order`
FOR EACH ROW
UPDATE goods
SET goods_number = goods_number + OLD.buy_number
WHERE goods_id = OLD.goods_id;

測試：DELETE FROM \`order\` WHERE goods_id = 1; → nokiaN85 庫存回補。

4. AFTER UPDATE：修改訂單數量同步調整庫存

CREATE TRIGGER update_goods_number
AFTER UPDATE ON `order`
FOR EACH ROW
UPDATE goods
SET goods_number = goods_number + OLD.buy_number - NEW.buy_number
WHERE goods_id = NEW.goods_id;

邏輯：先加回舊數量，再扣新數量，等價於 goods_number = goods_number - (NEW.buy_number - OLD.buy_number)。

NEW / OLD 可用性速查

最佳實踐與限制

• 觸發器內避免複雜邏輯、外部調用、長耗時操作，影響主表寫入效能
• 觸發器與主表操作在同一事務，觸發器報錯會導致主操作回滾
• 不支援在觸發器中對同一表執行 SELECT (會報錯 "Can't update table ... in stored function/trigger")
• 大量導入數據時建議暫時禁用觸發器，完成後重建
• 使用 SHOW TRIGGERS 查看、DROP TRIGGER 刪除