MySQL 觸發器實戰：單語句與多語句觸發器、查看與刪除

MySQL 觸發器實戰：單語句與多語句觸發器、查看與刪除

一、單語句觸發器

語法：CREATE TRIGGER name timing event ON table FOR EACH ROW statement

-- 在 t_user 插入前，自動記錄時間到 trigger_time 表
CREATE TRIGGER t_user_trig
BEFORE INSERT ON t_user
FOR EACH ROW
INSERT INTO trigger_time VALUES (NOW());

測試：

INSERT INTO t_user VALUES (2, 'wang', 0, 'abcdef', NOW(), 'a');
SELECT * FROM trigger_time;
-- 輸出：插入時刻的一行記錄

二、多語句觸發器（需自定義分隔符）

DELIMITER &&
CREATE TRIGGER t_user_trig_del
AFTER DELETE ON t_user
FOR EACH ROW
BEGIN
  INSERT INTO trigger_time VALUES ('2000-01-01 10:10:00');
  INSERT INTO trigger_time VALUES ('2001-01-01 11:11:00');
END &&
DELIMITER ;

關鍵點：

• DELIMITER && 將語句結束符暫改為 &&，避免 BEGIN...END 內部 ; 被誤判為結束。
• BEGIN ... END 包裹多條語句。
• 結束後 DELIMITER ; 恢復默認分隔符。

三、查看觸發器

-- 查看指定觸發器詳細定義
SELECT * FROM information_schema.triggers WHERE trigger_name = 't_user_trig';

-- 查看當前庫所有觸發器
SHOW TRIGGERS;

四、刪除觸發器

DROP TRIGGER IF EXISTS t_user_trig;
DROP TRIGGER IF EXISTS t_user_trig_del;

五、觸發器設計最佳實踐

1. 輕量原則：觸發器內避免複雜計算、跨表大量寫入、網絡調用，否則嚴重拖慢主表 DML 性能。
2. 避免遞歸：觸發器內操作同一表再次觸發同類事件 → 死循環。需用會話變量標記：IF @in_trigger IS NULL THEN SET @in_trigger=1; ... SET @in_trigger=NULL; END IF;。
3. 事務一致性：觸發器與觸發語句在同一事務，觸發器報錯 → 主操作回滾。
4. 備份/遷移注意：mysqldump --triggers 導出，遷移時檢查 DEFINER 權限（參考 1449 錯誤處理）。
5. 監控：SHOW STATUS LIKE 'Trigger%' 觀察觸發頻率。

適用場景

• 審計日誌（插入/更新/刪除自動寫入日誌表）。
• 衍生列維護（如 updated_at = NOW()、計數器增減）。
• 跨表同步（主表變更同步更新統計表）。
• 數據完整性補充檢查（應用層已驗證，資料庫層二次防線）。

一句話總結： 觸發器是「資料庫層面的 AOP」，用得好是利器，用得重是毒藥——保持輕量、防遞歸、做監控。