MySQL 觸發器 完整指南：語法、實戰案例與最佳實踐

MySQL 觸發器 完整指南：語法、實戰案例與最佳實踐

觸發器是 MySQL 中強大的自動化機制，能在資料異動前後自動執行預定義邏輯。本文系統講解觸發器四要素、建立語法、刪除方法，並以「訂單-商品」庫存聯動為核心案例，展示 INSERT/UPDATE/DELETE 三種觸發場景的實戰寫法。

一、觸發器四大核心要素

二、建立觸發器語法

DELIMITER $  -- 修改結束符，避免 BEGIN...END 內部 ; 衝突

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
FOR EACH ROW
BEGIN
    -- 觸發邏輯：可包含多條 SQL
    SQL 语句;
END$

DELIMITER ;  -- 恢復預設結束符

三、刪除觸發器

DROP TRIGGER [IF EXISTS] trigger_name;

四、實戰案例：訂單與商品庫存聯動

測試表結構：

-- 商品表
CREATE TABLE goods (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    num INT  -- 庫存數量
);

-- 訂單表
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    gid INT,      -- 商品 ID
    much INT      -- 購買數量
);

1. 下單扣減庫存 (AFTER INSERT)

CREATE TRIGGER tg_after_insert_order
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    UPDATE goods 
    SET num = num - NEW.much 
    WHERE id = NEW.gid;
END$

關鍵點：NEW 代表新插入的行，NEW.欄位名 取得欄位值。

2. 取消訂單恢復庫存 (AFTER DELETE)

CREATE TRIGGER tg_after_delete_order
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
    UPDATE goods 
    SET num = num + OLD.much 
    WHERE id = OLD.gid;
END$

關鍵點：OLD 代表被刪除的行。

3. 修改訂單調整庫存 (AFTER UPDATE)

CREATE TRIGGER tg_after_update_order
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    UPDATE goods 
    SET num = num + OLD.much - NEW.much 
    WHERE id = OLD.gid;
END$

邏輯：先加回舊數量，再扣減新數量，差值即為庫存變動量。

五、BEFORE vs AFTER 關鍵差異

六、進階技巧與注意事項

1. 多條語句：BEGIN...END 區塊支援複雜邏輯（條件判斷、迴圈、變數聲明）
2. 錯誤處理：觸發器內拋出錯誤會導致原 DML 回滾，可用於業務約束
3. 效能考量：避免觸發器中執行耗時查詢或跨庫操作，影響寫入延遲
4. 遞歸防護：觸發器修改同表可能引發遞歸，需謹慎設計
5. 權限需求：建立觸發器需 TRIGGER 權限 (MySQL 5.1.6+)
6. 備份還原：mysqldump --triggers 導出觸發器，預設已包含

七、查看現有觸發器

SHOW TRIGGERS;
-- 或查詢系統表
SELECT * FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA='db_name';

總結

MySQL 觸發器是實現資料完整性、業務聯動、審計日誌的利器。掌握 NEW/OLD 伪行引用、BEFORE/AFTER 時機選擇、複雜邏輯封裝等核心技巧，能讓資料庫層承擔更多業務邏輯，減少應用層複雜度。但需權衡效能與維護性，避免過度依賴導致「隱藏邏輯」難以追蹤。