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

MySQL 觸發器完整指南：創建、NEW/OLD、BEFORE/AFTER、調用存儲過程、錯誤處理與實戰案例

編輯：MySQL 綜合教學

MySQL 觸發器完整指南：創建、NEW/OLD、BEFORE/AFTER、調用存儲過程、錯誤處理與實戰案例

觸發器是與表事件（INSERT/UPDATE/DELETE）綁定的特殊存儲程序，自動執行，無法手動調用。本文系統講解語法、NEW/OLD 伪表行、時機選擇、多語句封裝、調用存儲過程、錯誤傳播機制，並給出審計日志、數據同步、業務約束等實戰案例。

一、語法結構

CREATE TRIGGER `trigger_name`
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON `table_name`
FOR EACH ROW
BEGIN
    -- 觸發器體
END;

BEFORE：事件前執行，可修改 NEW 值、阻止操作（拋異常）
AFTER：事件後執行，不可修改 NEW，可基於變更做後續動作
同一表、同一事件、同一時機只能有一個觸發器（5.7+ 支持多個，需指定 FOLLOWS/PRECEDES）

二、NEW 與 OLD 伪表行

事件	OLD	NEW	可修改 NEW
INSERT	無	新行值	BEFORE 可修改
UPDATE	舊行值	新行值	BEFORE 可修改
DELETE	舊行值	無	不可

引用：NEW.col_name、OLD.col_name
修改：SET NEW.col_name = value;（僅 BEFORE INSERT/UPDATE）
OLD 唯讀，NEW 在 BEFORE 可寫

三、管理命令

-- 查看所有
SHOW TRIGGERS;

-- 查看具體
SHOW CREATE TRIGGER `trigger_name`;

-- 刪除
DROP TRIGGER IF EXISTS `trigger_name`;

四、實戰案例 1：審計日志（AFTER INSERT/UPDATE/DELETE）

CREATE TABLE `audit_log` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `table_name` VARCHAR(64) NOT NULL,
    `operation` ENUM('INSERT','UPDATE','DELETE') NOT NULL,
    `old_data` JSON,
    `new_data` JSON,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

DELIMITER $$
CREATE TRIGGER `trg_person_audit_insert`
AFTER INSERT ON `person`
FOR EACH ROW
BEGIN
    INSERT INTO `audit_log` (`table_name`, `operation`, `new_data`)
    VALUES ('person', 'INSERT', JSON_OBJECT('id', NEW.id, 'username', NEW.username, 'age', NEW.age));
END $$

CREATE TRIGGER `trg_person_audit_update`
AFTER UPDATE ON `person`
FOR EACH ROW
BEGIN
    INSERT INTO `audit_log` (`table_name`, `operation`, `old_data`, `new_data`)
    VALUES ('person', 'UPDATE', 
        JSON_OBJECT('id', OLD.id, 'username', OLD.username, 'age', OLD.age),
        JSON_OBJECT('id', NEW.id, 'username', NEW.username, 'age', NEW.age));
END $$

CREATE TRIGGER `trg_person_audit_delete`
AFTER DELETE ON `person`
FOR EACH ROW
BEGIN
    INSERT INTO `audit_log` (`table_name`, `operation`, `old_data`)
    VALUES ('person', 'DELETE', JSON_OBJECT('id', OLD.id, 'username', OLD.username, 'age', OLD.age));
END $$
DELIMITER ;

五、實戰案例 2：業務約束（BEFORE INSERT/UPDATE 修正數據）

DELIMITER $$
CREATE TRIGGER `trg_person_age_fix`
BEFORE INSERT ON `person`
FOR EACH ROW
BEGIN
    IF NEW.age < 0 THEN SET NEW.age = 0;
    ELSEIF NEW.age > 100 THEN SET NEW.age = 100;
    END IF;
    SET NEW.username = TRIM(NEW.username);
END $$

CREATE TRIGGER `trg_person_age_fix_update`
BEFORE UPDATE ON `person`
FOR EACH ROW
BEGIN
    IF NEW.age < 0 THEN SET NEW.age = 0;
    ELSEIF NEW.age > 100 THEN SET NEW.age = 100;
    END IF;
END $$
DELIMITER ;

六、實戰案例 3：觸發器調用存儲過程（複雜邏輯復用）

DELIMITER $$
CREATE PROCEDURE `sp_validate_age` (INOUT p_age INT)
BEGIN
    IF p_age < 0 THEN SET p_age = 0;
    ELSEIF p_age > 100 THEN SET p_age = 100;
    END IF;
END $$

CREATE TRIGGER `trg_person_age_sp`
BEFORE UPDATE ON `person`
FOR EACH ROW
BEGIN
    CALL sp_validate_age(NEW.age);
END $$
DELIMITER ;

七、錯誤處理與事務傳播

BEFORE 觸發器拋出異常 → 觸發事件的 SQL 回滾，AFTER 不執行
AFTER 觸發器拋出異常 → 事件 SQL 已提交（隱式事務），無法回滾主操作，但會阻塞後續操作
事務表：主操作 + 觸發器在同一事務，整體回滾
非事務表：主操作已寫入，觸發器失敗不回滾主操作

-- 捕獲異常示例
DELIMITER $$
CREATE TRIGGER `trg_safe`
BEFORE INSERT ON `t`
FOR EACH ROW
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        INSERT INTO `err_log`(`msg`) VALUES('Trigger error');
        -- 可選：RESIGNAL 拋出導致主操作回滾
        RESIGNAL;
    END;
    -- 可能出錯的邏輯
END $$
DELIMITER ;