MySQL 觸發器實戰：自動化資料維護與審計日誌完整指南

什麼是觸發器？

觸發器是針對特定表的特定事件（INSERT/UPDATE/DELETE）自動執行的程式碼。MySQL 5.0+ 引入，5.x 版本持續增強。

一、基礎語法

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
trigger_body

關鍵字說明

• BEFORE/AFTER：事件前/後執行
• FOR EACH ROW：逐列觸發（MySQL 僅支援行級）
• NEW：新資料行（INSERT/UPDATE 可用）
• OLD：舊資料行（UPDATE/DELETE 可用）

二、實戰範例：自動統計字元總數

場景

表 data(name VARCHAR(255)) 插入新名稱時，自動更新 chars(count INT) 累計字元數。

建表與初始化

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

測試效果

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

三、進階應用場景

1. 審計日誌（記錄所有變更）

CREATE TABLE audit_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  table_name VARCHAR(64),
  operation ENUM('INSERT','UPDATE','DELETE'),
  old_data JSON,
  new_data JSON,
  user VARCHAR(64),
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER audit_users AFTER INSERT ON users
FOR EACH ROW
INSERT INTO audit_log (table_name, operation, new_data, user)
VALUES ('users', 'INSERT', CAST(CONCAT('{"id":',NEW.id,',"name":"',NEW.name,'"}') AS JSON), CURRENT_USER());

CREATE TRIGGER audit_users_upd AFTER UPDATE ON users
FOR EACH ROW
INSERT INTO audit_log (table_name, operation, old_data, new_data, user)
VALUES ('users', 'UPDATE', 
  CAST(CONCAT('{"id":',OLD.id,',"name":"',OLD.name,'"}') AS JSON),
  CAST(CONCAT('{"id":',NEW.id,',"name":"',NEW.name,'"}') AS JSON),
  CURRENT_USER());

2. 衍生欄位自動維護

CREATE TRIGGER calc_total BEFORE INSERT ON orders
FOR EACH ROW
SET NEW.total = NEW.qty * NEW.price * (1 - IFNULL(NEW.discount,0));

3. 跨表級聯同步

CREATE TRIGGER sync_profile AFTER UPDATE ON users
FOR EACH ROW
UPDATE profiles SET email=NEW.email WHERE user_id=NEW.id;

四、管理與除錯

查看觸發器

SHOW TRIGGERS;
SHOW TRIGGERS FROM dbname;
SHOW CREATE TRIGGER trigger_name;

刪除觸發器

DROP TRIGGER [IF EXISTS] trigger_name;

除錯技巧

• 觸發器內無法直接 SELECT 回傳結果，改寫入臨時表或日誌表
• 使用 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='...' 拋出自定義錯誤（5.5+）
• 避免觸發器中執行耗時操作，影響主表寫入效能

五、限制與注意事項

• MySQL 不支援語句級觸發器（FOR EACH STATEMENT）
• 觸發器不能呼叫會隱式/顯式提交的語句（COMMIT、LOCK TABLES 等）
• 外鍵約束觸發順序：BEFORE 觸發器 → 約束檢查 → AFTER 觸發器
• 備份工具 mysqldump 預設包含觸發器（--triggers）