主表 test、歷史表 test_hisy,記錄操作類型、時間、前後值。
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(20) COMMENT 'insert/update/delete',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
DELIMITER //
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 ;
DELIMITER //
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 ;
DELIMITER //
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 |
|---|---|---|
| INSERT | 新值 ✓ | 不可用 |
| UPDATE | 新值 ✓ | 舊值 ✓ |
| DELETE | 不可用 | 舊值 ✓ |
IF NEW.status != OLD.status THEN ... 只記錄狀態變更mysqldump --triggers 包含觸發器;SHOW TRIGGERS 查看TRIGGER 權限 (MySQL 5.1.6+)SHOW TRIGGERS\G
DROP TRIGGER IF EXISTS `t_after_insert_test`;