您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 觸發器完整指南：語法、四大要素、NEW/OLD 伪表、四大場景實戰 (同步庫存、級聯刪除、審計日誌、自動預警)

編輯：MySQL 綜合教學

MySQL 觸發器完整指南

觸發器是針對表的 INSERT/UPDATE/DELETE 事件，在 BEFORE/AFTER 時機自動執行的存儲程序。本文系統講解語法、四大要素、NEW/OLD 伪表用法，並以「商品-訂單」經典模型實戰四大高頻場景。

核心語法

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    -- SQL 語句 (可用 NEW.col / OLD.col)
END;

DROP TRIGGER [schema.]trigger_name;
SHOW TRIGGERS;

四大要素與限制

要素	說明
監視地點	永久表 (不支援視圖/臨時表)
監視事件	INSERT / UPDATE / DELETE
觸發時機	BEFORE (可修改 NEW 值) / AFTER (不可修改)
執行程序	單條或多條 SQL (BEGIN...END)

⚠️ **同表、同事件、同時機只能建一個觸發器** (5.7+ 支援多個，須指定 FOLLOWS/PRECEDES)。
⚠️ **不支援事務控制** (無法 COMMIT/ROLLBACK)；MyISAM 下觸發器失敗不回滾主表操作，InnoDB 下觸發器與主操作在同一事務 (原子)。
⚠️ **不支援返回結果集** 的存儲過程調用 (可用 OUT 參數)。

NEW / OLD 伪表詳解

事件	OLD	NEW
INSERT	無	新插入行 (可修改 BEFORE 中)
UPDATE	修改前舊值	修改後新值 (可修改 BEFORE 中)
DELETE	被刪除行	無

實戰場景 (表：goods(id, name, num), orders(id, gid, much))

場景一：下單後自動扣減庫存 (AFTER INSERT)

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

場景二：取消訂單歸還庫存 (AFTER DELETE)

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

場景三：修改訂單數量同步調整庫存 (AFTER UPDATE)

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

場景四：庫存預警 (BEFORE UPDATE on goods)

CREATE TRIGGER trg_goods_low_stock
BEFORE UPDATE ON goods
FOR EACH ROW
BEGIN
    IF NEW.num < 10 AND OLD.num >= 10 THEN
        INSERT INTO warn_log (goods_id, msg, created_at) 
        VALUES (NEW.id, CONCAT('商品 ', NEW.name, ' 庫存不足 10'), NOW());
    END IF;
END;

進階應用：審計日誌 (通用模板)

CREATE TABLE audit_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(64), operation ENUM('INSERT','UPDATE','DELETE'),
    old_data JSON, new_data JSON, 
    operator VARCHAR(64), op_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 以 orders 表為例
CREATE TRIGGER trg_orders_audit
AFTER INSERT ON orders FOR EACH ROW
    INSERT INTO audit_log (table_name, operation, new_data, operator) 
    VALUES ('orders', 'INSERT', JSON_OBJECT('id', NEW.id, 'gid', NEW.gid, 'much', NEW.much), CURRENT_USER());

CREATE TRIGGER trg_orders_audit_upd
AFTER UPDATE ON orders FOR EACH ROW
    INSERT INTO audit_log (table_name, operation, old_data, new_data, operator) 
    VALUES ('orders', 'UPDATE', 
        JSON_OBJECT('id', OLD.id, 'gid', OLD.gid, 'much', OLD.much),
        JSON_OBJECT('id', NEW.id, 'gid', NEW.gid, 'much', NEW.much), 
        CURRENT_USER());

CREATE TRIGGER trg_orders_audit_del
AFTER DELETE ON orders FOR EACH ROW
    INSERT INTO audit_log (table_name, operation, old_data, operator) 
    VALUES ('orders', 'DELETE', JSON_OBJECT('id', OLD.id, 'gid', OLD.gid, 'much', OLD.much), CURRENT_USER());