MySQL 觸發器基礎操作完全指南

MySQL 觸發器基礎操作完全指南

在 MySQL 資料庫中，資料表是儲存與操作資料的邏輯結構，而觸發器則是用來實現由特定資料表事件所觸發的自動化操作。當資料庫系統執行 INSERT、UPDATE 或 DELETE 等資料表事件時，相關聯的觸發器會被激活，進而執行其內部定義的 SQL 語句。觸發器的核心操作包含建立、查看與刪除三大類。

觸發器屬於資料庫物件之一，其運作機制類似程式語言中的函數，都需要宣告與執行邏輯。但觸發器的執行並非由程式主動呼叫，也非手動啟動，而是由特定事件（如資料異動）自動觸發。例如：在學生資料表中包含學生姓名與總人數欄位，每當新增一筆學生記錄時，總人數必須同步更新。此時可建立一個觸發器，於每次 INSERT 操作前自動執行計算總人數的邏輯，確保資料一致性。MySQL 僅在 DELETE、INSERT、UPDATE 語句執行時激活觸發器，其他 SQL 指令則不會觸發。觸發器能有效強化資料表的資料完整性約束與業務規則，是資料庫設計中不可或缺的工具。

1. 建立觸發器

依據觸發時執行的 SQL 語句數量，觸發器可分為「單一執行語句」與「多重執行語句」兩種類型。

1.1 單一執行語句觸發器

語法結構如下：

CREATE TRIGGER trigger_name BEFORE | AFTER trigger_event ON table_name FOR EACH ROW trigger_stmt

參數說明：

• trigger_name：觸發器名稱，不可重複，建議命名為 trigger_xxx 或 tri_xxx。
• BEFORE / AFTER：指定觸發執行時機，BEFORE 表示事件前執行，AFTER 表示事件後執行。
• trigger_event：觸發事件，包含 INSERT、DELETE、UPDATE。
• table_name：觸發事件作用的目標資料表。
• FOR EACH ROW：表示任何一筆記錄的操作滿足觸發條件皆會啟動該觸發器。
• trigger_stmt：觸發器激活後執行的 SQL 語句。

範例：

CREATE TRIGGER tri_diarytime BEFORE INSERT ON t_dept FOR EACH ROW INSERT INTO t_diary VALUES (NULL, 't_dept', NOW());

功能：在部門表 t_dept 插入新記錄前，自動向日誌表 t_diary 寫入當前時間記錄。

1.2 多重執行語句觸發器

語法結構如下：

CREATE TRIGGER trigger_name BEFORE | AFTER trigger_event ON table_name FOR EACH ROW BEGIN trigger_stmt_list END

與單一語句版本的差別在於多了 BEGIN 與 END 關鍵字，中間可包含多條以分號分隔的 SQL 語句。由於 MySQL 預設以分號為語句結束符，在建立觸發器時需使用 DELIMITER 臨時變更結束符號（例如 DELIMITER $$），避免衝突。

範例：

DELIMITER $$
CREATE TRIGGER tri_diarytime2 AFTER INSERT ON t_dept FOR EACH ROW
BEGIN
    INSERT INTO t_diary VALUES (NULL, 't_dept', NOW());
    INSERT INTO t_diary VALUES (NULL, 't_dept', NOW());
END $$
DELIMITER ;

2. 查看觸發器

2.1 使用 SHOW TRIGGERS 指令

SHOW TRIGGERS \G

2.2 查詢系統資料表 information_schema.triggers

USE information_schema;
SELECT * FROM triggers \G
SELECT * FROM triggers WHERE trigger_name = 'tri_diarytime2' \G

3. 刪除觸發器

DROP TRIGGER trigger_name;

以上即為 MySQL 觸發器的基礎操作完整介紹，掌握這些技巧能讓你更靈活地實現資料庫層面的自動化邏輯與資料完整性維護。