MySQL 觸發器完整指南：語法、NEW/OLD、執行順序、事務回滾與實戰案例

MySQL 觸發器完整指南：語法詳解、NEW/OLD 變量、執行順序與事務交互

觸發器是與表綁定的數據庫對象，當表發生 INSERT/UPDATE/DELETE 事件時自動執行。本文系統講解創建語法、六種觸發時機、BEGIN...END 塊、變量聲明、NEW/OLD 伪表行、查看刪除、執行順序與事務回滾機制，並給出「學生-班級」同步計數器實戰案例。

一、創建語法

CREATE TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name
    FOR EACH ROW
    trigger_stmt

• trigger_time：BEFORE | AFTER
• trigger_event：INSERT | UPDATE | DELETE
• trigger_stmt：單條 SQL 或 BEGIN ... END 複合語句

六種組合：BEFORE INSERT、AFTER INSERT、BEFORE UPDATE、AFTER UPDATE、BEFORE DELETE、AFTER DELETE。
限制：同一表同一時機同一事件只能建一個觸發器，單表最多 6 個。

二、觸發事件擴展

• LOAD DATA INFILE → 等價多次 INSERT，觸發 INSERT 觸發器。
• REPLACE INTO：
  - 無主鍵衝突 → 觸發 INSERT
  - 有主鍵衝突 → 觸發 DELETE (舊行) + INSERT (新行)

三、BEGIN ... END 與 DELIMITER

DELIMITER $$
CREATE TRIGGER tri_stuInsert
AFTER INSERT ON student
FOR EACH ROW
BEGIN
    DECLARE c INT;
    SET c = (SELECT stuCount FROM class WHERE classID = NEW.classID);
    UPDATE class SET stuCount = c + 1 WHERE classID = NEW.classID;
END$$
DELIMITER ;

• DELIMITER $$ 將語句結束符臨時改為 $$，避免複合語句內分號被誤判為結束。
• 用完必須 DELIMITER ; 恢複。

四、變量聲明與賦值

DECLARE var_name [, var_name]... type [DEFAULT value];
SET var_name = expr [, var_name = expr] ...;

• 只能在 BEGIN ... END 開頭聲明，類型支持所有 MySQL 數據類型。
• 默認值為 NULL，可用表達式初始化。

五、NEW 與 OLD 伪表行（核心）

訪問方式：NEW.col_name、OLD.col_name。
關鍵特性：在 BEFORE 觸發器中 SET NEW.col = value 可修改即將寫入的值，且不會再次觸發觸發器（避免遞歸）。

六、查看與刪除

SHOW TRIGGERS [FROM db_name];
DROP TRIGGER [IF EXISTS] [db_name.]trigger_name;

七、執行順序與事務交互（InnoDB 事務表）

1. BEFORE 觸發器執行
2. 檢查約束、執行 DML（INSERT/UPDATE/DELETE）
3. AFTER 觸發器執行

事務回滾規則：

• BEFORE 觸發器報錯 → DML 不執行，事務隱式回滾。
• DML 執行報錯（如唯一鍵衝突） → AFTER 觸發器不會觸發，事務回滾。
• AFTER 觸發器報錯 → DML 已執行，但事務會回滾（InnoDB 保證原子性）。

八、完整實戰案例：學生表插入自動維護班級人數

-- 表結構
CREATE TABLE class (
    classID INT PRIMARY KEY,
    stuCount INT DEFAULT 0
);
CREATE TABLE student (
    stuID INT PRIMARY KEY AUTO_INCREMENT,
    classID INT,
    name VARCHAR(20),
    FOREIGN KEY (classID) REFERENCES class(classID)
);

-- 觸發器
DELIMITER $$
CREATE TRIGGER tri_stuInsert
AFTER INSERT ON student
FOR EACH ROW
BEGIN
    DECLARE c INT;
    SELECT stuCount INTO c FROM class WHERE classID = NEW.classID;
    UPDATE class SET stuCount = c + 1 WHERE classID = NEW.classID;
END$$
DELIMITER ;

-- 測試
INSERT INTO class VALUES (1, 0);
INSERT INTO student (classID, name) VALUES (1, 'Alice');
INSERT INTO student (classID, name) VALUES (1, 'Bob');
SELECT * FROM class;  -- stuCount = 2

九、避坑指南

• 觸發器內不能調用 COMMIT/ROLLBACK/START TRANSACTION（隱式事務上下文）。
• 觸發器內不能對觸發表再做 DML（會報錯 Can't update table 'x' in stored function/trigger），需通過別表中轉。
• 大批量導入時觸發器逐行執行極慢，建議導入前禁用觸發器（DROP TRIGGER），導入後重建並手動同步計數器。
• 避免在觸發器中做複雜查詢/外部調用，影響主業務延遲。

觸發器適合「輕量級、強一致性、單表/雙表聯動」場景；複雜業務邏輯請上層應用或存儲過程處理，保持數據庫層純粹高效。

1. 上一篇文章： Linux crontab 實現 MySQL 定時自動備份：腳本編寫、權限配置、日誌輪轉全流程
2. 下一篇文章： SQL 執行順序深度解析：基礎查詢與 LEFT JOIN 結果集構建過程

MySQL 綜合教學

MySQL Windows 安裝圖解教程：自訂目錄、資料安全、服務配置完整流程

Windows 環境安裝 MySQL 選擇「Custom」自

Windows 建立 MySQL ODBC 資料來源 (DSN) 圖文步驟

Windows 建立 MySQL ODBC 資料來源 (DS

MySQL 視圖全攻略：創建、查詢、修改、更新與權限控制實戰

視圖是虛擬表，不存儲數據，只保存查詢定義。它能簡化複雜查詢、

MySQL SQL 語句優化實戰：10 大核心技巧與避坑指南

MySQL SQL 語句優化實戰：10 大核心技巧與避坑指南