MySQL 觸發器基礎入門：語法詳解、NEW/OLD 使用、創建測試、管理命令、權限控制

前言

觸發器是響應表 DML 事件（INSERT/UPDATE/DELETE）自動執行的特殊存儲過程，提供比約束更精細的數據控制能力。本文從零開始，系統講解觸發器概念、語法、NEW/OLD 使用、實戰測試、管理命令，適合初學者快速上手。

一、觸發器核心概念與作用

二、創建語法詳解

CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name
trigger_time trigger_event ON tbl_name
FOR EACH ROW trigger_body

trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }

關鍵子句說明

trigger_name

建議命名：表名_事件縮寫_時機，如 ttlsa_users_ai（after insert），長度 ≤ 64 字符

DEFINER

執行時權限檢查上下文，確保安全性

trigger_time

BEFORE：行寫入前（可修改 NEW）；AFTER：行寫入後（不可修改 NEW）

trigger_event

INSERT（含 LOAD DATA/REPLACE）、UPDATE、DELETE（含 REPLACE）；不觸發：DROP TABLE、TRUNCATE TABLE、分區刪除

FOR EACH ROW

行級觸發（MySQL 僅支援行級），每行執行一次

trigger_body

SQL 語句；復合語句用 BEGIN ... END；支援流控、變量、異常處理；受函數限制（不可返回結果集、不可動態 SQL 等）

NEW 與 OLD 使用規則

錯誤示例：ERROR 1363 (HY000): There is no OLD row in on INSERT trigger

三、完整實戰演示

1. 建表

CREATE TABLE ttlsa_users (
  uid INT(11) UNSIGNED PRIMARY KEY,
  username VARCHAR(40) NOT NULL,
  password VARCHAR(40) NOT NULL,
  createtime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE ttlsa_users3 (
  uid INT(11) UNSIGNED,
  userinfo VARCHAR(200)
);

2. 創建觸發器

DELIMITER //
CREATE TRIGGER ttlsa_users_ai
AFTER INSERT ON ttlsa_users FOR EACH ROW
  INSERT INTO ttlsa_users3 (uid, userinfo) VALUES (NEW.uid, JSON_OBJECT(NEW.uid, NEW.username, NEW.password));
//
CREATE TRIGGER ttlsa_users_au
AFTER UPDATE ON ttlsa_users FOR EACH ROW
  UPDATE ttlsa_users3 SET userinfo = JSON_OBJECT(NEW.uid, NEW.username, NEW.password) WHERE uid = OLD.uid;
//
DELIMITER ;

3. 測試驗證

INSERT INTO ttlsa_users VALUES (890, 'xuhh', MD5('abc'));
SELECT * FROM ttlsa_users3;
-- {"uid":890,"username":"xuhh","password":"900150983cd24fb0d6963f7d28e17f72"}

UPDATE ttlsa_users SET password = 'test_update' WHERE uid = 890;
SELECT * FROM ttlsa_users3;
-- {"uid":890,"username":"xuhh","password":"test_update"}

四、管理命令大全

-- 列出所有觸發器
SHOW TRIGGERS;
SHOW TRIGGERS LIKE '%ttlsa%';
SHOW TRIGGERS FROM database_name;

-- 查看創建語句
SHOW CREATE TRIGGER ttlsa_users_ai;

-- 信息元數據查詢
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA='test' AND TRIGGER_NAME='ttlsa_users_au'\G

-- 刪除觸發器
DROP TRIGGER ttlsa_users_ai;

五、權限控制

-- 授權創建觸發器權限
GRANT TRIGGER ON database_name.table_name TO 'user'@'host';

-- 回收權限
REVOKE TRIGGER ON database_name.table_name FROM 'user'@'host';

注意： MySQL 5.7+ 將 TRIGGER 權限從 SUPER 中分離，單獨授予更安全。

六、限制與注意事項

• 不能在觸發器中調用會返回數據給客戶端的存儲過程/函數
• 不能使用動態 SQL（PREPARE/EXECUTE）
• 不能使用顯式/隱式事務控制（START TRANSACTION/COMMIT/ROLLBACK）
• 觸發器內報錯會導致觸發語句回滾
• 遞歸觸發深度受 max_sp_recursion_depth 限制（默認 0，禁用遞歸）
• 備份時加 --triggers，遷移時注意 DEFINER 賬號是否存在