觸發器語法結構

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON tbl_name
FOR EACH ROW
trigger_body;

子句	說明
trigger_name	庫級唯一，建議前綴 tg_，如 tg_bu_users_email
BEFORE/AFTER	觸發時機：行變更前/後
INSERT/UPDATE/DELETE	觸發事件，每表每事件**僅能建一個**觸發器（5.7+ 支援多個，需 FOLLOWS/PRECEDES 指定順序）
FOR EACH ROW	行級觸發（MySQL 僅支援行級，無語句級）
trigger_body	單句或 BEGIN ... END 複合語句，需自定義 DELIMITER

新舊行引用：NEW 與 OLD

事件	NEW	OLD
INSERT	新行值（可修改）	不可用
UPDATE	新值（可修改）	舊值（只讀）
DELETE	不可用	舊值（只讀）

實戰範例：審計日誌與衍生表同步

建表

CREATE TABLE `account` (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  balance DECIMAL(19,4) NOT NULL DEFAULT 0,
  version INT NOT NULL DEFAULT 0
);

CREATE TABLE `account_log` (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  account_id BIGINT NOT NULL,
  change_amount DECIMAL(19,4) NOT NULL,
  balance_after DECIMAL(19,4) NOT NULL,
  op_type ENUM('DEPOSIT','WITHDRAW','TRANSFER') NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_account_time (account_id, created_at)
);

BEFORE UPDATE：業務規則校驗 + 版本號遞增

DELIMITER $$
CREATE TRIGGER tg_bu_account_balance
BEFORE UPDATE ON `account`
FOR EACH ROW
BEGIN
  -- 禁止負餘額
  IF NEW.balance < 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Balance cannot be negative';
  END IF;
  -- 樂觀鎖版本號自增
  SET NEW.version = OLD.version + 1;
END$$
DELIMITER ;

AFTER UPDATE：寫入審計日誌

DELIMITER $$
CREATE TRIGGER tg_au_account_log
AFTER UPDATE ON `account`
FOR EACH ROW
BEGIN
  IF NEW.balance <> OLD.balance THEN
    INSERT INTO account_log (account_id, change_amount, balance_after, op_type)
    VALUES (NEW.id, NEW.balance - OLD.balance, NEW.balance,
            CASE WHEN NEW.balance > OLD.balance THEN 'DEPOSIT' ELSE 'WITHDRAW' END);
  END IF;
END$$
DELIMITER ;

BEFORE DELETE：軟刪除攔截

DELIMITER $$
CREATE TRIGGER tg_bd_account_soft_delete
BEFORE DELETE ON `account`
FOR EACH ROW
BEGIN
  -- 實際業務建議用 deleted_at 軟刪除，此處演示攔截硬刪
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Hard delete forbidden, use soft delete';
END$$
DELIMITER ;

執行順序與事務邊界（核心知識點）

1. BEFORE 觸發器 → 行變更（含約束檢查、外鍵、唯一索引） → AFTER 觸發器。
2. 所有觸發器與觸發語句在**同一事務**中：任一環節拋錯（含 SIGNAL），**整個事務回滾**，觸發器內修改不會持久化。
3. BEFORE 觸發器中修改 NEW.col 可影響最終寫入值；AFTER 觸發器中 NEW 僅可讀。
4. 觸發器內**不能**顯式 COMMIT/ROLLBACK、不能鎖表、不能調用會話級別語句（PREPARE、動態 SQL 受限）。

查看、修改、刪除觸發器

-- 查看
SHOW TRIGGERS;
SELECT * FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA='db_name';

-- 刪除
DROP TRIGGER IF EXISTS tg_bu_account_balance;

-- 修改：必須先 DROP 再 CREATE（無 ALTER TRIGGER）

效能與最佳實踐

• 輕量化：觸發器內只做極簡邏輯（校驗、寫日誌、計數器維護），嚴禁複雜查詢、外部 HTTP 調用。
• 避免遞歸：觸發器修改同表再觸發 → 無限循環。用會話變數標記：IF @skip_trigger THEN SET @skip_trigger=FALSE; ELSE ... END IF;
• 批量操作影響：LOAD DATA、REPLACE、INSERT ... ON DUPLICATE KEY UPDATE **不觸發** BEFORE/AFTER INSERT/UPDATE 觸發器（視版本而定，5.7+ 部分觸發）。大量寫入時觸發器開銷顯著。
• 替代方案：應用層事件發射（Kafka/RocketMQ）+ 異步消費者寫審計表，解耦且效能可控。

權限要求

• 建立：TRIGGER 權限（5.1.6+ 需 SUPER，8.0 獨立 TRIGGER 權限）。
• 執行：觸發器以 DEFINER 權限執行（預設建立者），若定義者被刪除，觸發器執行失敗。建議專用 trig_admin@localhost 建立並維護。