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

MySQL 觸發器完整指南：建立、OLD/NEW、實戰案例(庫存扣減防超賣)

編輯：MySQL 綜合教學

MySQL 觸發器完整指南：建立、OLD/NEW、實戰案例(庫存扣減防超賣)

觸發器是針對表的 DML 操作 (INSERT/UPDATE/DELETE) 自動執行的預定義邏輯，適用於資料完整性約束、審計日誌、衍生資料維護等場景。

建立語法

DELIMITER $$  -- 修改結束符，避免觸發器內 ; 衝突

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
FOR EACH ROW
BEGIN
    -- 觸發器主體 (可包含複合語句)
    -- 限制：不能呼叫存儲過程、不能使用顯式事務控制
END$$

DELIMITER ;  -- 恢復結束符

觸發時機與事件對照

時機	事件	典型用途
BEFORE	INSERT	資料驗證、補全預設值、格式化
BEFORE	UPDATE	防止非法修改、記錄變更前值
BEFORE	DELETE	軟刪除攔截、關聯檢查
AFTER	INSERT	同步衍生表、發送通知、審計日誌
AFTER	UPDATE	同步更新關聯表、觸發重算
AFTER	DELETE	清理關聯資料、記錄刪除日誌

OLD 與 NEW 伪列：核心概念

操作	OLD	NEW	可修改
INSERT	無 (所有欄位 NULL)	新插入的行	✅ BEFORE 可修改 NEW
UPDATE	修改前的行	修改後的行	✅ BEFORE 可修改 NEW
DELETE	被刪除的行	無 (所有欄位 NULL)	❌

語法：`OLD.col_name`、`NEW.col_name` (必須加前綴，不可直接用欄位名)

管理指令

-- 查看所有觸發器
SHOW TRIGGERS;

-- 查看特定資料庫
SHOW TRIGGERS FROM db_name;

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

實戰案例：庫存扣減防超賣

場景：`goods` 表有 `num` (庫存)，`ord` 表記錄訂單 `much` (購買量)。下單時檢查庫存，不足則按實際庫存出貨，不允許負數。

DELIMITER $$

CREATE TRIGGER trg_check_stock
BEFORE INSERT ON ord
FOR EACH ROW
BEGIN
    DECLARE v_stock INT DEFAULT 0;
    
    -- 獲取當前庫存
    SELECT num INTO v_stock FROM goods WHERE gid = NEW.gid;
    
    -- 庫存不足：調整購買量為實際庫存
    IF NEW.much > v_stock THEN
        SET NEW.much = v_stock;
    END IF;
    
    -- 扣減庫存
    UPDATE goods SET num = num - NEW.much WHERE gid = NEW.gid;
END$$

DELIMITER ;

進階案例：審計日誌自動記錄

CREATE TABLE user_audit (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    action ENUM('INSERT','UPDATE','DELETE') NOT NULL,
    old_data JSON,
    new_data JSON,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER $$
CREATE TRIGGER trg_user_audit
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_audit (user_id, action, old_data, new_data)
    VALUES (
        NEW.id,
        'UPDATE',
        JSON_OBJECT('name', OLD.name, 'email', OLD.email, 'status', OLD.status),
        JSON_OBJECT('name', NEW.name, 'email', NEW.email, 'status', NEW.status)
    );
END$$
DELIMITER ;