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

MySQL 觸發器基礎教程：語法、權限、NEW/OLD 伪表與實戰案例

編輯：MySQL 綜合教學

一、觸發器核心概念

特殊存儲過程，由 INSERT/UPDATE/DELETE 事件自動觸發
行級觸發：FOR EACH ROW，每影響一行執行一次
時間點：BEFORE（寫入前，可修改新值）/ AFTER（寫入後，不可修改）
權限：需要 CREATE TRIGGER、TRIGGER 權限（MySQL 5.7+）

二、創建語法詳解

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`	建議格式：`表名_事件縮寫_時間`，如 `users_ai` (after insert)
`DEFINER`	執行時權限檢查上下文，預設創建者
`FOR EACH ROW`	行級觸發，唯一支援模式
`trigger_body`	SQL 語句，可用 `BEGIN ... END` 包裹複合語句

NEW / OLD 伪表列訪問規則

事件	可用	不可用	典型用途
INSERT	NEW	OLD	獲取自增 ID、補全默認值、同步異構表
UPDATE	NEW, OLD	-	比較新舊值、記錄變更日誌、條件阻止更新
DELETE	OLD	NEW	歸檔刪除數據、級聯清理、防誤刪

引用方式：NEW.col_name、OLD.col_name

三、完整實戰案例：用戶表變更同步至 JSON 存儲表

3.1 表結構

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

CREATE TABLE ttlsa_users_sync (
  uid INT UNSIGNED PRIMARY KEY,
  userinfo JSON  -- 或 VARCHAR 存儲 JSON 字符串
);

3.2 觸發器定義

DELIMITER //

CREATE TRIGGER ttlsa_users_ai
AFTER INSERT ON ttlsa_users
FOR EACH ROW
BEGIN
  INSERT INTO ttlsa_users_sync (uid, userinfo)
  VALUES (NEW.uid, JSON_OBJECT('uid', NEW.uid, 'username', NEW.username, 'password', NEW.password));
END //

CREATE TRIGGER ttlsa_users_au
AFTER UPDATE ON ttlsa_users
FOR EACH ROW
BEGIN
  UPDATE ttlsa_users_sync 
  SET userinfo = JSON_OBJECT('uid', NEW.uid, 'username', NEW.username, 'password', NEW.password)
  WHERE uid = OLD.uid;
END //

CREATE TRIGGER ttlsa_users_ad
AFTER DELETE ON ttlsa_users
FOR EACH ROW
BEGIN
  DELETE FROM ttlsa_users_sync WHERE uid = OLD.uid;
END //

DELIMITER ;

3.3 測試驗證

  操作命令
  查看所有SHOW TRIGGERS;
  按模式查看SHOW TRIGGERS LIKE 'ttlsa%';
  查看指定庫SHOW TRIGGERS FROM db_name;
  查看創建語句SHOW CREATE TRIGGER trigger_name;
  查詢元數據SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA='db' AND TRIGGER_NAME='name'\G
  刪除觸發器DROP TRIGGER [IF EXISTS] trigger_name;


五、權限授予與回收