MySQL 觸發器可在資料異動前/後自動執行邏輯。本文提供完整建表、三類觸發器（AFTER INSERT、BEFORE UPDATE、BEFORE DELETE）、NEW/OLD 伪表列用法、DELIMITER 處理。
測試表結構
-- 主表
CREATE TABLE `test` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL DEFAULT '',
`type` VARCHAR(100),
`create_time` DATETIME,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 歷史/備份表
CREATE TABLE `test_hisy` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL DEFAULT '',
`type` VARCHAR(100),
`create_time` DATETIME,
`operation` VARCHAR(100) COMMENT '操作類型',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
1. AFTER INSERT：新增同步（type='1' 才備份）
DELIMITER //
DROP TRIGGER IF EXISTS t_after_insert_test//
CREATE TRIGGER t_after_insert_test
AFTER INSERT ON `test`
FOR EACH ROW
BEGIN
IF NEW.type = '1' THEN
INSERT INTO `test_hisy` (`name`, `type`, `create_time`, `operation`)
VALUES (NEW.`name`, NEW.`type`, NEW.`create_time`, 'insert');
END IF;
END//
DELIMITER ;
2. BEFORE UPDATE：修改前備份舊值（type='2' 才備份）
DELIMITER //
DROP TRIGGER IF EXISTS t_before_update_test//
CREATE TRIGGER t_before_update_test
BEFORE UPDATE ON `test`
FOR EACH ROW
BEGIN
IF NEW.`type` = '2' THEN
INSERT INTO `test_hisy` (`name`, `type`, `create_time`, `operation`)
VALUES (OLD.`name`, OLD.`type`, OLD.`create_time`, 'update');
END IF;
END//
DELIMITER ;
3. BEFORE DELETE：刪除前備份完整舊列
DELIMITER //
DROP TRIGGER IF EXISTS t_before_delete_test//
CREATE TRIGGER t_before_delete_test
BEFORE DELETE ON `test`
FOR EACH ROW
BEGIN
INSERT INTO `test_hisy` (`name`, `type`, `create_time`, `operation`)
VALUES (OLD.`name`, OLD.`type`, OLD.`create_time`, 'delete');
END//
DELIMITER ;
NEW / OLD 伪表列速查
