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

什麼是觸發器？

觸發器是與表關聯的命名資料庫對象，當該表發生特定事件（INSERT/UPDATE/DELETE）時自動執行定義的 SQL。分為 BEFORE（行前）與 AFTER（行後）兩種觸發時機。

語法結構

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
  -- SQL 語句；可用 NEW.col / OLD.col 訪問新/舊行
END;

實戰案例：下單自動扣庫存、退單回補、改單調整

建表

CREATE TABLE goods (
  goods_id INT PRIMARY KEY AUTO_INCREMENT,
  goods_name VARCHAR(64),
  shop_price DECIMAL(10,2),
  goods_number INT NOT NULL DEFAULT 0
) ENGINE=InnoDB;

CREATE TABLE `order` (
  order_id INT PRIMARY KEY AUTO_INCREMENT,
  goods_id INT NOT NULL,
  buy_number INT NOT NULL
) ENGINE=InnoDB;

初始化資料

INSERT INTO goods VALUES
(1,'Nokia N85',2000,35),
(2,'iPhone 4S',4500,30),
(3,'Lumia',5000,40),
(4,'Samsung',4200,20);

1. AFTER INSERT：下單扣庫存

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

2. AFTER DELETE：退單回補

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

3. AFTER UPDATE：改單調整差值

CREATE TRIGGER trg_order_after_update
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;

測試驗證

-- 下單 5 台 Nokia
INSERT INTO `order` (goods_id, buy_number) VALUES (1, 5);
SELECT goods_number FROM goods WHERE goods_id=1;  -- 30

-- 修改訂單改為 8 台
UPDATE `order` SET buy_number=8 WHERE order_id=1;
SELECT goods_number FROM goods WHERE goods_id=1;  -- 27 (30 - 8 + 5)

-- 刪除訂單
DELETE FROM `order` WHERE order_id=1;
SELECT goods_number FROM goods WHERE goods_id=1;  -- 35 (27 + 8)

關鍵點總結

• NEW：INSERT/UPDATE 可用，代表新行值；DELETE 不可用。
• OLD：UPDATE/DELETE 可用，代表舊行值；INSERT 不可用。
• 觸發器內不能對同一表執行 INSERT/UPDATE/DELETE（會遞歸觸發），只能操作其他表。
• BEFORE 觸發器常用於資料校驗/清洗（如 SET NEW.col = TRIM(NEW.col)）；AFTER 用於業務聯動。
• 觸發器與原語句在同一事務，任一失敗整體回滾，保證一致性。

查看與管理

SHOW TRIGGERS;
DROP TRIGGER IF EXISTS trg_order_after_insert;

觸發器將業務不變量封裝在資料庫層，避免應用層漏調、重複代碼，是資料完整性的最後一道防線。