您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

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

編輯：MySQL 資料庫相關知識

前言與程式碼撰寫慣例

本教程旨在系統性地介紹 MySQL 5.0 中引入的觸發器功能。為了方便閱讀，所有實際代碼示例均採用等寬字體顯示，並模擬 MySQL 客戶端的輸出風格。例如：

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> 和 -> 提示符，您可直接複製至客戶端執行。所有範例均在 SuSE 9.2 Linux、MySQL 5.0.3 版本上測試通過，理論上兼容更新版本及 Windows、Sparc、HP-UX 等平台。

為什麼需要觸發器？

MySQL 5.0 導入觸發器主要基於以下考量：

滿足早期版本用戶長期以來的功能需求。
踐行支援 ANSI SQL 標準特性的承諾。
提供資料層面的主動防禦機制，攔截非法資料寫入。
允許在 DML 操作（INSERT、UPDATE、DELETE）前後攔截或修改行為。
支援在會話層級審計資料變更軌跡。

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

閱讀本文建議先掌握《MySQL 存儲過程》的核心概念。觸發器體內可使用復合語句、流程控制、變量聲明、條件與異常處理等構造，但受限於函數的相同限制——不可在觸發器中直接存取表（如 SELECT、INSERT、UPDATE、DELETE、LOCK 等語句均被禁止）。

觸發器語法詳解

1. 命名規範

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

名稱長度上限 64 字元，建議採用 表名_觸發時機_事件 格式（如 t26_bu 表示表 t26 的 BEFORE UPDATE 觸發器）。

2. 觸發時機

BEFORE：在行變更前執行，可修改 NEW 值。
AFTER：在行變更後執行，不可修改資料。

3. 觸發事件

支援 INSERT、UPDATE、DELETE 三種 DML 操作。同一張表的同一事件同一時機只能建立一個觸發器。

4. 綁定資料表

觸發器隸屬於特定表，僅在該表發生對應操作時激活。

5. 執行粒度

FOR EACH ROW 表示逐行觸發，而非語句級觸發。

6. 觸發器主體

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

權限模型

創建觸發器需 CREATE TRIGGER 權限（早期版本由 Root 等超級權限代行）。未來版本將引入專用權限：

GRANT CREATE TRIGGER ON <table_name> TO <user_list>;
REVOKE CREATE TRIGGER ON <table_name> FROM <user_list>;

新舊行標識符：NEW 與 OLD

觸發器內部通過 NEW.column_name 和 OLD.column_name 區分新舊值：

INSERT：僅 NEW 合法。
DELETE：僅 OLD 合法。
UPDATE：兩者皆可使用。

範例：捕獲 UPDATE 前後值

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

若 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   |
+------------------------+------+

可見觸發器成功攔截並重寫了插入值。

進階應用：以觸發器實現 CHECK 約束

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

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;//

配合主鍵特性，違規插入將觸發重複鍵錯誤（Error 23000），達到約束效果。