MySQL 觸發器應用實戰：自動化資料管理與審計完整教程

觸發器核心概念

觸發器是響應特定表特定事件（INSERT/UPDATE/DELETE）自動執行的程式碼。MySQL 5.x 引入並持續完善，支援行級觸發（FOR EACH ROW）。

快速上手範例：字元數自動累計

建表

CREATE TABLE data (name VARCHAR(255));
CREATE TABLE chars (count INT);
INSERT INTO chars VALUES (0);

建立觸發器

CREATE TRIGGER t1 AFTER INSERT ON data
FOR EACH ROW
UPDATE chars SET count = count + CHAR_LENGTH(NEW.name);

驗證

mysql> SHOW TRIGGERS\G
Trigger: t1
Event: INSERT
Table: data
Statement: UPDATE chars SET count = count + CHAR_LENGTH(NEW.name)
Timing: AFTER

測試

INSERT INTO data VALUES ('Alice'), ('Bob'), ('Charlie');
SELECT * FROM chars;  -- count = 15 (5+3+7)

四大經典應用場景

1. 審計日誌（全變更留痕）

CREATE TABLE audit_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tbl VARCHAR(64), op ENUM('I','U','D'),
  old_json JSON, new_json JSON,
  usr VARCHAR(64), ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER audit_ins AFTER INSERT ON users
FOR EACH ROW
INSERT INTO audit_log (tbl,op,new_json,usr) VALUES ('users','I',
  JSON_OBJECT('id',NEW.id,'name',NEW.name), CURRENT_USER());

CREATE TRIGGER audit_upd AFTER UPDATE ON users
FOR EACH ROW
INSERT INTO audit_log (tbl,op,old_json,new_json,usr) VALUES ('users','U',
  JSON_OBJECT('id',OLD.id,'name',OLD.name),
  JSON_OBJECT('id',NEW.id,'name',NEW.name), CURRENT_USER());

2. 衍生欄位自動計算

CREATE TRIGGER calc_order_total BEFORE INSERT ON orders
FOR EACH ROW
SET NEW.amount = NEW.qty * NEW.unit_price * (1 - COALESCE(NEW.discount,0));

3. 跨表資料同步

CREATE TRIGGER sync_email AFTER UPDATE ON users
FOR EACH ROW
IF NEW.email <> OLD.email THEN
  UPDATE profiles SET email=NEW.email WHERE user_id=NEW.id;
END IF;

4. 業務規則強制執行

CREATE TRIGGER check_stock BEFORE INSERT ON order_items
FOR EACH ROW
BEGIN
  DECLARE stock INT;
  SELECT quantity INTO stock FROM products WHERE id=NEW.product_id;
  IF stock < NEW.qty THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='庫存不足';
  END IF;
END;

管理指令速查

關鍵限制

• 僅支援行級（FOR EACH ROW），不支援語句級
• 不可使用 COMMIT/ROLLBACK/LOCK TABLES
• 外鍵約束順序：BEFORE 觸發器 → 檢查約束 → AFTER 觸發器
• 5.5+ 才支援 SIGNAL 拋出自定義錯誤