MySQL 觸發器實戰入門：建立、查看、測試與審計日誌完整範例

觸發器基礎語法

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON tbl_name FOR EACH ROW
trigger_stmt

• BEFORE：語句執行前（可修改 NEW 值）。
• AFTER：語句執行後。
• NEW.col：新資料列值（INSERT/UPDATE）。
• OLD.col：舊資料列值（UPDATE/DELETE）。
• 需 SUPER 權限。

範例一：自動統計字元總數

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

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

測試：

INSERT INTO data VALUES ('Sue'), ('Jane');
SELECT * FROM chars;  -- count = 7

查看觸發器

SHOW TRIGGERS\G
-- 輸出：Trigger: t1, Event: INSERT, Table: data, Statement: UPDATE ..., Timing: AFTER

刪除觸發器

DROP TRIGGER t1;

範例二：審計日誌（記錄帳戶餘額變動）

CREATE TABLE accounts (id INT PRIMARY KEY, balance FLOAT);
CREATE TABLE audit (
  id INT, balance FLOAT,
  user VARCHAR(50), time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER audit_bal AFTER UPDATE ON accounts
FOR EACH ROW
INSERT INTO audit (id, balance, user)
VALUES (OLD.id, NEW.balance, CURRENT_USER());

測試：

UPDATE accounts SET balance = 1000 WHERE id = 1;
SELECT * FROM audit;  -- 記錄 id=1, balance=1000, user=root@localhost, time=...

進階提示

• 多語句：用 BEGIN ... END 包裹，裡面可用 IF、LOOP 等存儲程序語法。
• 遞迴防護：觸發器內修改同表會再次觸發，需用條件判斷或變數標記避免無限迴圈。
• 效能：大量批次操作時，觸發器逐列執行影響效能，考慮應用層寫入審計表或用 binlog 解析。
• 版本：MySQL 5.0+ 支援，5.7+ 增強 SHOW TRIGGERS 權限控制，8.0 支援 INFORMATION_SCHEMA.TRIGGERS 查詢。