MySQL 觸發器完整指南：語法、NEW/OLD、事務特性、審計日誌實戰與避坑

什麼是觸發器

與表綁定的**被動執行**程序：表發生 INSERT/UPDATE/DELETE 時，自動觸發預定義 SQL。**觸發動作與觸發器體在同一事務中**（原子性：全成或全滾）。

語法結構

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
  -- trigger body
END;

關鍵點

• BEFORE：約束/修正數據（可修改 NEW）、阻止操作（SIGNAL）
• AFTER：日誌記錄、衍生表同步、異步通知（不可修改 NEW）
• FOR EACH ROW：行級觸發（MySQL 僅支持行級）
• NEW：INSERT/UPDATE 可用，指向新行
• OLD：UPDATE/DELETE 可用，指向舊行

實戰：審計日誌自動記錄

基表

CREATE TABLE `user` (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  account VARCHAR(255), name VARCHAR(255), address VARCHAR(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_history` (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  operate_type VARCHAR(50) NOT NULL,
  operate_time DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

三個觸發器

DELIMITER ;;

-- INSERT 觸發
CREATE TRIGGER tri_insert_user AFTER INSERT ON `user`
FOR EACH ROW
BEGIN
  INSERT INTO user_history(user_id, operate_type, operate_time)
  VALUES (NEW.id, 'INSERT', NOW());
END;;

-- UPDATE 觸發
CREATE TRIGGER tri_update_user AFTER UPDATE ON `user`
FOR EACH ROW
BEGIN
  INSERT INTO user_history(user_id, operate_type, operate_time)
  VALUES (NEW.id, 'UPDATE', NOW());
END;;

-- DELETE 觸發
CREATE TRIGGER tri_delete_user AFTER DELETE ON `user`
FOR EACH ROW
BEGIN
  INSERT INTO user_history(user_id, operate_type, operate_time)
  VALUES (OLD.id, 'DELETE', NOW());
END;;

DELIMITER ;

驗證

INSERT INTO user(account,name,address) VALUES('u1','u1','u1');
INSERT INTO user(account,name,address) VALUES('u2','u2','u2');
UPDATE user SET name='u3',account='u3',address='u3' WHERE name='u1';
DELETE FROM user WHERE name='u2';

SELECT * FROM user_history;
-- 4 行記錄：2 INSERT, 1 UPDATE, 1 DELETE

進階用法

BEFORE 觸發器做數據校驗/修正

CREATE TRIGGER tri_user_before_ins BEFORE INSERT ON `user`
FOR EACH ROW
BEGIN
  IF NEW.account IS NULL OR NEW.account = '' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'account 不能為空';
  END IF;
  SET NEW.account = TRIM(LOWER(NEW.account));  -- 統一格式
END;

獲取變更前後值對比

CREATE TRIGGER tri_user_audit AFTER UPDATE ON `user`
FOR EACH ROW
BEGIN
  IF OLD.name <> NEW.name THEN
    INSERT INTO audit_log(table_name, pk_id, field, old_val, new_val)
    VALUES ('user', NEW.id, 'name', OLD.name, NEW.name);
  END IF;
  -- 同理比對其他字段
END;

避坑指南

查看與管理

SHOW TRIGGERS;
SELECT * FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA='db_name';
DROP TRIGGER trigger_name;

總結

觸發器是「數據庫的 AOP」：**橫切關注點**（審計、同步、約束）下沉利器。**克制使用、保持簡單、嚴禁遞歸、邏輯上移應用層**，才能發揮優勢而不成累贅。