萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 觸發器完整實戰指南:創建、類型、NEW/OLD 伪表、避坑與存儲過程對比

觸發器核心語法

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    -- 觸發器主體:SQL 語句(可多條,用分號分隔)
END;

六大觸發時機類型

類型觸發時機典型用途
BEFORE INSERT插入前數據校驗、自動填充創建時間、生成 UUID
AFTER INSERT插入後同步關聯表、寫審計日誌、發送通知
BEFORE UPDATE更新前防止關鍵字段修改、自動更新修改時間
AFTER UPDATE更新後異步同步、記錄變更歷史
BEFORE DELETE刪除前軟刪除攔截、關聯檢查、歸檔
AFTER DELETE刪除後清理關聯數據、統計更新

NEW / OLD 伪表:訪問行數據的關鍵

伪表INSERTUPDATEDELETE
NEW新插入行 ✅更新後新值 ✅不可用 ❌
OLD不可用 ❌更新前舊值 ✅被刪除行 ✅

訪問語法

NEW.column_name   -- 新值
OLD.column_name   -- 舊值

實戰案例集錦

案例 1:同步備份表(AFTER INSERT)

CREATE TABLE tab1 (id VARCHAR(11) PRIMARY KEY);
CREATE TABLE tab2 (id VARCHAR(11) PRIMARY KEY);

CREATE TRIGGER t_afterinsert_on_tab1
AFTER INSERT ON tab1
FOR EACH ROW
BEGIN
    INSERT INTO tab2 (id) VALUES (NEW.id);
END;

案例 2:自動維護庫存扣減(AFTER INSERT)

CREATE TRIGGER trg_order_after_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    UPDATE products 
    SET stock = stock - NEW.quantity 
    WHERE id = NEW.product_id;
END;

案例 3:自動更新時間戳(BEFORE UPDATE)

CREATE TRIGGER trg_user_before_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
    -- 防止惡意修改創建時間
    SET NEW.created_at = OLD.created_at;
END;

案例 4:軟刪除實現(BEFORE DELETE)

CREATE TRIGGER trg_article_before_delete
BEFORE DELETE ON articles
FOR EACH ROW
BEGIN
    -- 攔截物理刪除,轉為邏輯刪除
    UPDATE articles SET deleted_at = NOW(), status = 'deleted' WHERE id = OLD.id;
    -- 取消本次物理刪除(MySQL 不支持直接 CANCEL,需應用層配合或改用 UPDATE)
    -- 此處演示邏輯,實際需應用層發起 UPDATE 而非 DELETE
END;

觸發器管理命令

-- 查看所有觸發器
SHOW TRIGGERS\G

-- 查看特定表觸發器
SHOW TRIGGERS FROM db_name LIKE 'table_name'\G

-- 查看觸發器定義
SHOW CREATE TRIGGER trigger_name\G

-- 刪除觸發器
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;

關鍵限制與避坑指南

1. 不能調用返回結果集的存儲過程

觸發器內不可 `CALL` 返回結果集的存儲過程,可調用通過 OUT/INOUT 參數返回數據的過程。

2. 避免遞歸觸發(死循環)

-- ❌ 錯誤:觸發器中更新同表觸發自身
CREATE TRIGGER trg_bad BEFORE UPDATE ON t FOR EACH ROW
    UPDATE t SET col = NEW.col WHERE id = NEW.id;  -- 死循環

-- ✅ 正確:使用 SET 直接賦值 NEW 列
CREATE TRIGGER trg_good BEFORE UPDATE ON t FOR EACH ROW
    SET NEW.updated_at = NOW();  -- 直接修改 NEW 伪表列,不觸發遞歸

3. 不能使用事務控制語句

觸發器內**不能**使用 `START TRANSACTION`、`COMMIT`、`ROLLBACK`。觸發器運行在觸發語句的事務上下文中。

4. 外鍵約束觸發順序

外鍵 `ON UPDATE/DELETE CASCADE` 動作**不會**觸發關聯表的觸發器(MySQL 行為)。

5. 權限要求

創建觸發器需 TRIGGER 權限(MySQL 5.7.2+),超級權限用戶默認擁有。

觸發器 vs 存儲過程:選型指南

維度觸發器存儲過程
觸發方式自動(DML 事件)手動調用 (CALL)
參數無參數支持 IN/OUT/INOUT
返回值無直接返回可通過參數/結果集返回
事務控制無(屬於外部事務)可控制事務邊界
適用場景數據完整性、審計、同步、自動化維護複雜業務邏輯封裝、批量處理、報表生成

總結

MySQL 觸發器是**數據層自動化邏輯**的利器。掌握 BEFORE/AFTER × INSERT/UPDATE/DELETE 六類時機、**NEW/OLD 伪表** 訪問模式、**SET NEW.col = val** 避免遞歸、無事務控制/無結果集返回 限制。用於「數據必然伴隨的動作」(審計、同步、校驗、時間戳),複雜業務流程轉交存儲過程或應用層。謹記:**觸發器隱蔽、難調試、影響寫入性能**,能在應用層解決的堅決不下沉數據庫層。

copyright © 萬盛學電腦網 all rights reserved