什麼是 Trigger？

Trigger（觸發器） 是一種特殊的預存程序，不需 CALL 呼叫，而是在 INSERT、UPDATE、DELETE 等 DML 事件發生時，由 MySQL 自動觸發執行。它提供比標準約束（PK/FK/CHECK）更精細、更複雜的資料完整性與業務邏輯控制能力。

六大核心用途

類別	典型場景
安全性控制	依資料值/時間限制操作（如非工作時段禁止修改、股價漲幅不超 10%）
稽核追蹤	記錄誰在何時執行何種 DML，寫入稽核表
複雜完整性	跨表檢查、非標準約束（如期貨保證金檢查）、可變預設值
關聯級聯	刪除/更新主表時自動級聯處理從表（CASCADE/SET NULL/SET DEFAULT）
即時同步	同步複寫表資料至報表庫/緩存表
自動計算與告警	帳戶餘額低於閾值自動發送通知、自動統計聚合欄位

語法速查

建立觸發器

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON tbl_name
FOR EACH ROW
BEGIN
    -- 觸發器本體：可使用 NEW、OLD、流程控制、預存程序呼叫
END;

• BEFORE：在 DML 執行前觸發，可修改 NEW.col 值（如資料清洗、驗證）。
• AFTER：在 DML 執行後觸發，NEW/OLD 唯讀，適合寫入稽核、同步其他表。
• FOR EACH ROW：逐列觸發（目前 MySQL 僅支援行級觸發器）。
• 同一張表、同一事件、同一時機（BEFORE/AFTER）只能建立一個觸發器。

刪除觸發器

DROP TRIGGER [schema_name.]trigger_name;

查看觸發器

SHOW TRIGGERS;
-- 或
SELECT * FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA='db_name';

實戰範例

範例 1：單一陳述式 BEFORE INSERT 累加金額

CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
CREATE TRIGGER ins_sum BEFORE INSERT ON account
FOR EACH ROW SET @sum = @sum + NEW.amount;

SET @sum = 0;
INSERT INTO account VALUES (5,12.5);
SELECT @sum;  -- 12.5

範例 2：BEFORE UPDATE 限制金額範圍 0~100

CREATE TRIGGER upd_check BEFORE UPDATE ON account
FOR EACH ROW
BEGIN
    IF NEW.amount < 0 THEN SET NEW.amount = 0;
    ELSEIF NEW.amount > 100 THEN SET NEW.amount = 100;
    END IF;
END;

範例 3：多陳述式 AFTER INSERT 同步多張表

DELIMITER //
CREATE TRIGGER testref AFTER INSERT ON test1
FOR EACH ROW
BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1;
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END//
DELIMITER ;

範例 4：跨表級聯刪除（模擬 FK CASCADE）

CREATE TRIGGER del_auth_cascade AFTER DELETE ON auths
FOR EACH ROW
BEGIN
    DELETE FROM books WHERE author_code = OLD.author_code;
    DELETE FROM royalties WHERE author_code = OLD.author_code;
END;

NEW / OLD 伪列存取規則

事件	可用	不可用	可修改 NEW
INSERT	NEW.col	OLD.col	BEFORE 時可修改
UPDATE	NEW.col, OLD.col	—	BEFORE 時可修改
DELETE	OLD.col	NEW.col	否

錯誤處理與事務邊界

• BEFORE 觸發器失敗 → 對應行 DML 不執行。
• BEFORE 成功、行 DML 成功 → 執行 AFTER 觸發器。
• 任何階段報錯 → 整個 SQL 陳述式回滾（InnoDB 事務表）；MyISAM 非事務表則無法回滾，已執行的更改生效。
• 觸發器內不能使用 START TRANSACTION、COMMIT、ROLLBACK。
• 觸發器內不能呼叫會回傳結果集給客戶端的預存程序。

效能與架構建議

1. 大批量資料操作慎用觸發器：逐列觸發會放大 CPU/IO 開銷，百萬列匯入時建議暫時 DROP TRIGGER，事後補資料。
2. 優先用外鍵約束：FK 由存儲引擎內部實作，效能優於觸發器模擬 CASCADE。
3. 複雜邏輯抽離至預存程序：觸發器僅 CALL sp_name()，便於單元測試與版本控制。
4. 避免觸發器巢狀過深：觸發器內修改其他表又觸發該表觸發器，易造成遞迴或死鎖。
5. InnoDB 才保證原子性：MyISAM 表上觸發器失敗不會回滾前序操作，資料一致性無保障。

版本升級注意

從 MySQL 5.0.10 以前版本升級時，必須先刪除所有觸發器，升級完成後重建，否則 DROP TRIGGER 將失效。