您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 5 觸發器完整教程：從語法到實戰應用

編輯：MySQL 綜合教學

前言：關於代碼呈現風格

為了讓讀者輕松區分程式碼與正文，本教程採用等寬字體（Courier）顯示所有實際代碼片段。範例如下：

mysql> DROP FUNCTION f;
Query OK, 0 rows affected (0.00 sec)

較長的範例會在關鍵行添加註釋，並以「<--」符號標示重點：

mysql> CREATE PROCEDURE p ()
-> BEGIN
-> /* This procedure does nothing */ <--
-> END;//
Query OK, 0 rows affected (0.00 sec)

部分範例會省略「mysql>」與「->」等系統提示符，方便直接複製到 MySQL 客戶端執行。所有範例均在 SUSE 9.2 Linux、MySQL 5.0.3 公共版測試通過，理論上相容於更新版本及 Windows、Sparc、HP-UX 等平台。若執行異常，建議諮詢資深 MySQL 使用者尋求支援。

為什麼需要觸發器？

MySQL 5.0 引入觸發器支援，主要基於以下考量：

早期版本用戶長期呼喚此功能
履行支援所有 ANSI 標準特性的承諾
可檢查或阻擋無效數據寫入資料庫
能修改或取消 INSERT、UPDATE、DELETE 語句
可在會話中監控數據變更動作

前置知識：存儲過程與函數

假設讀者已熟悉「MySQL 存儲過程」，因為觸發器可使用函數中允許的語句，包括：

複合語句（BEGIN / END）
流程控制（IF, CASE, WHILE, LOOP, REPEAT, LEAVE, ITERATE）
變量聲明（DECLARE）與指派（SET）
條件聲明與異常處理聲明

關鍵限制： 函數中不能訪問表，因此以下語句在函數與觸發器中均屬非法：

ALTER, CACHE INDEX, CALL, COMMIT, CREATE, DELETE, DROP, FLUSH PRIVILEGES,
GRANT, INSERT, KILL, LOCK, OPTIMIZE, REPAIR, REPLACE, REVOKE, ROLLBACK,
SAVEPOINT, SELECT FROM table, SET system variable, SET TRANSACTION, SHOW,
START TRANSACTION, TRUNCATE, UPDATE

提醒：觸發器相對較新，可能存在缺陷。請勿在含重要數據的生產庫直接使用，建議先在測試環境驗證，並確認建立觸發器時的預設資料庫正確。

觸發器語法詳細解析

1. 命名規則

CREATE TRIGGER <觸發器名稱> 
{ BEFORE | AFTER } { INSERT | UPDATE | DELETE }
ON <表名稱> FOR EACH ROW <觸發器SQL語句>

名稱長度上限 64 字元，命名慣例與其他 MySQL 物件一致
建議採用「表名_觸發時機_事件」格式，如 t26_bu 表示表 t26 的 BEFORE UPDATE 觸發器

2. 觸發時機（BEFORE | AFTER）

指定觸發器在事件發生之前或之後執行。

3. 觸發事件（INSERT | UPDATE | DELETE）

定義觸發器響應的 DML 操作類型。

4. 所屬表

觸發器隸屬於特定表，該表執行對應 DML 時激活。同一表無法為同一事件建立兩個觸發器。

5. 執行間隔（FOR EACH ROW）

表示觸發器逐行執行，而非對整張表一次性執行。

6. 觸發語句

可包含任何合法 SQL 語句（含複合語句），但受函數相同限制。

權限需求

建立觸發器需較高權限，Root 使用者足夠。未來版本可能引入專屬 CREATE TRIGGER 權限：

GRANT CREATE TRIGGER ON <表名稱> TO <用戶或用戶列表>;
REVOKE CREATE TRIGGER ON <表名稱> FROM <用戶或用戶列表>;

NEW 與 OLD：識別新舊列值

觸發器內部使用 NEW.column_name 與 OLD.column_name 區分新舊值（技術上稱為「過渡變量」）：

INSERT： 僅 NEW 合法
DELETE： 僅 OLD 合法
UPDATE： NEW 與 OLD 皆可使用

範例：UPDATE 中同時使用 NEW 與 OLD

CREATE TRIGGER t21_au BEFORE UPDATE ON t22 FOR EACH ROW
BEGIN
  SET @old = OLD.s1;
  SET @new = NEW.s1;
END;//

若 t21 表 s1 列原值為 55，執行 UPDATE t21 SET s1 = s1 + 1 後，@old=55、@new=56。

實戰範例：建立與測試觸發器

建立帶觸發器的表

CREATE TABLE t22 (s1 INTEGER)// 
CREATE TRIGGER t22_bi BEFORE INSERT ON t22 FOR EACH ROW
BEGIN
  SET @x = 'Trigger was activated!';
  SET NEW.s1 = 55;
END;//

執行插入並觀察結果

mysql> INSERT INTO t22 VALUES (1)//
mysql> SELECT @x, t22.* FROM t22//
+------------------------+------+
| @x                     | s1   |
+------------------------+------+
| Trigger was activated! | 55   |
+------------------------+------+

可見觸發器已修改插入值（原輸入 1，實際存入 55）並設定了會話變量。

以觸發器實現 CHECK 約束

MySQL 不支援標準 SQL 的 CHECK 子句，但可用觸發器模擬：

CREATE TABLE t25 (s1 INT, s2 CHAR(5), PRIMARY KEY (s1)) ENGINE=INNODB//
CREATE TRIGGER t25_bi BEFORE INSERT ON t25 FOR EACH ROW
IF LEFT(NEW.s2,1)<>'A' THEN SET NEW.s1=0; END IF;//
CREATE TRIGGER t25_bu BEFORE UPDATE ON t25 FOR EACH ROW
IF LEFT(NEW.s2,1)<>'A' THEN SET NEW.s1=0; END IF;//

僅需 BEFORE INSERT/UPDATE；DELETE 不影響，AFTER 觸發器無法修改 NEW 變量。插入不符合條件的資料會導致主鍵衝突（s1=0 重複），達到阻擋效果。