如何使用MYSQL触发器，备份历史记录？

最新推荐文章于 2024-11-19 09:38:53 发布

Java&Mango

最新推荐文章于 2024-11-19 09:38:53 发布

阅读量802

点赞数

分类专栏：数据库文章标签： mysql 数据库 postgresql java

本文链接：https://blog.youkuaiyun.com/mangomango123/article/details/120698951

版权

数据库专栏收录该内容

3 篇文章

订阅专栏

该博客详细介绍了如何创建和使用数据库触发器来跟踪数据表`equipment_info`的变化。首先，创建了一个名为`lianxin_history`的历史数据库，然后在其中创建了与源表同构的`equipment_info`表，并添加了`OPERATE_TYPE`和`OPERATE_TIME`字段以记录操作类型和时间。接着，为源表`equipment_info`定义了三个触发器：INSERT、UPDATE和DELETE，分别在插入、更新和删除操作后向历史表中插入相应的记录，以完整保存数据操作历史。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、首先创建一个存放历史表的数据库。

-- 创建历史库
create database lianxin_history;

二、创建要备份的数据表

use lianxin_history;
-- 创建历史库中的equipment_info同构表以及增加两个记录操作状态字段
create table `equipment_info` select * from `lianxin_trace`.`equipment_info` where 1<0;
ALTER TABLE `equipment_info` 
ADD COLUMN `OPERATE_TYPE` VARCHAR(15) NULL COMMENT '操作类型：INSERT(新增)，UPDATE(更新)，DELETE(删除)',
ADD COLUMN `OPERATE_TIME` DATETIME NULL DEFAULT NOW() COMMENT '操作时间，自动生成';

三、创建触发器

1、新增的触发器。

-- 下方是创建数据源表触发器脚本
use lianxin_trace;

-- 向历史库创建一条新增记录（equipment_info表）
DROP TRIGGER IF EXISTS `lianxin_trace`.`equipment_info_AFTER_INSERT`;

DELIMITER $$
USE `lianxin_trace`$$
CREATE DEFINER = CURRENT_USER TRIGGER `lianxin_trace`.`equipment_info_AFTER_INSERT` AFTER INSERT ON `equipment_info` FOR EACH ROW
BEGIN
	INSERT INTO lianxin_history.equipment_info (EQUIPMENT_ID, EQUIPMENT_NAME, EQUIPMENT_CODE, PRODUCER, STATUS, PRODUCTION_TIME, ENTER_TIME, VALID_TIME, ESTIMATE, CONFIRM_RESULT, CONFIRM_TIME, CONFIRM_ITEM_INFO, USED, REMARKS, LEAVE_POS, RELA_PDA_FLAG, PDA_EQUIPMENT_CODE, ALIVE_FLAG, ADD_USER_ID, ADD_TIME, OPR_USER_ID, OPR_TIME, USE_LOCATION, OPERATE_TYPE, OPERATE_TIME) 
    VALUES (NEW.EQUIPMENT_ID, NEW.EQUIPMENT_NAME, NEW.EQUIPMENT_CODE, NEW.PRODUCER, NEW.STATUS, NEW.PRODUCTION_TIME, NEW.ENTER_TIME, NEW.VALID_TIME, NEW.ESTIMATE, NEW.CONFIRM_RESULT, NEW.CONFIRM_TIME, NEW.CONFIRM_ITEM_INFO, NEW.USED, NEW.REMARKS, NEW.LEAVE_POS, NEW.RELA_PDA_FLAG, NEW.PDA_EQUIPMENT_CODE, NEW.ALIVE_FLAG, NEW.ADD_USER_ID, NEW.ADD_TIME, NEW.OPR_USER_ID, NEW.OPR_TIME, NEW.USE_LOCATION, 'INSERT', NOW());
END$$
DELIMITER ;

2、修改的触发器

-- 下方是创建数据源表触发器脚本
use lianxin_trace;

-- 向历史库创建一条修改记录（equipment_info表）
DROP TRIGGER IF EXISTS `lianxin_trace`.`equipment_info_AFTER_UPDATE`;

DELIMITER $$
USE `lianxin_trace`$$
CREATE DEFINER = CURRENT_USER TRIGGER `lianxin_trace`.`equipment_info_AFTER_UPDATE` AFTER UPDATE ON `equipment_info` FOR EACH ROW
BEGIN
	INSERT INTO lianxin_history.equipment_info (EQUIPMENT_ID, EQUIPMENT_NAME, EQUIPMENT_CODE, PRODUCER, STATUS, PRODUCTION_TIME, ENTER_TIME, VALID_TIME, ESTIMATE, CONFIRM_RESULT, CONFIRM_TIME, CONFIRM_ITEM_INFO, USED, REMARKS, LEAVE_POS, RELA_PDA_FLAG, PDA_EQUIPMENT_CODE, ALIVE_FLAG, ADD_USER_ID, ADD_TIME, OPR_USER_ID, OPR_TIME, USE_LOCATION, OPERATE_TYPE, OPERATE_TIME) 
    VALUES (NEW.EQUIPMENT_ID, NEW.EQUIPMENT_NAME, NEW.EQUIPMENT_CODE, NEW.PRODUCER, NEW.STATUS, NEW.PRODUCTION_TIME, NEW.ENTER_TIME, NEW.VALID_TIME, NEW.ESTIMATE, NEW.CONFIRM_RESULT, NEW.CONFIRM_TIME, NEW.CONFIRM_ITEM_INFO, NEW.USED, NEW.REMARKS, NEW.LEAVE_POS, NEW.RELA_PDA_FLAG, NEW.PDA_EQUIPMENT_CODE, NEW.ALIVE_FLAG, NEW.ADD_USER_ID, NEW.ADD_TIME, NEW.OPR_USER_ID, NEW.OPR_TIME, NEW.USE_LOCATION, 'UPDATE', NOW());
END$$
DELIMITER ;

3、删除的触发器

-- 下方是创建数据源表触发器脚本
use lianxin_trace;

-- 向历史库创建一条删除记录（equipment_info表）
DROP TRIGGER IF EXISTS `lianxin_trace`.`equipment_info_BEFORE_DELETE`;

DELIMITER $$
USE `lianxin_trace`$$
CREATE DEFINER = CURRENT_USER TRIGGER `lianxin_trace`.`equipment_info_BEFORE_DELETE` BEFORE DELETE ON `equipment_info` FOR EACH ROW
BEGIN
	INSERT INTO lianxin_history.equipment_info (EQUIPMENT_ID, EQUIPMENT_NAME, EQUIPMENT_CODE, PRODUCER, STATUS, PRODUCTION_TIME, ENTER_TIME, VALID_TIME, ESTIMATE, CONFIRM_RESULT, CONFIRM_TIME, CONFIRM_ITEM_INFO, USED, REMARKS, LEAVE_POS, RELA_PDA_FLAG, PDA_EQUIPMENT_CODE, ALIVE_FLAG, ADD_USER_ID, ADD_TIME, OPR_USER_ID, OPR_TIME, USE_LOCATION, OPERATE_TYPE, OPERATE_TIME) 
    VALUES (OLD.EQUIPMENT_ID, OLD.EQUIPMENT_NAME, OLD.EQUIPMENT_CODE, OLD.PRODUCER, OLD.STATUS, OLD.PRODUCTION_TIME, OLD.ENTER_TIME, OLD.VALID_TIME, OLD.ESTIMATE, OLD.CONFIRM_RESULT, OLD.CONFIRM_TIME, OLD.CONFIRM_ITEM_INFO, OLD.USED, OLD.REMARKS, OLD.LEAVE_POS, OLD.RELA_PDA_FLAG, OLD.PDA_EQUIPMENT_CODE, OLD.ALIVE_FLAG, OLD.ADD_USER_ID, OLD.ADD_TIME, OLD.OPR_USER_ID, OLD.OPR_TIME, OLD.USE_LOCATION, 'DELETE', NOW());
END;$$
DELIMITER ;

测试结果：