MySql 触发器同步备份数据表记录

最新推荐文章于 2025-09-06 09:33:29 发布

转载最新推荐文章于 2025-09-06 09:33:29 发布 · 55 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/Warmsunshine/p/3957423.html

文章标签：

#数据库 #人工智能

本文介绍了一种使用MySQL触发器的方法，包括在插入新记录时复制数据到备份表，在删除记录前备份原始数据，以及记录删除操作的日志。通过这些触发器可以有效地监控和管理OPM_Alarm表的变化。

添加记录到新记录表


DELIMITER $$
USE `DB_Test`$$
CREATE
    /*!50017 DEFINER = 'root'@'%' */
    TRIGGER `InsertOPM_Alarm_trigger` BEFORE INSERT ON `OPM_Alarm` 
    FOR EACH ROW BEGIN
INSERT INTO OPM_Alarm_copy (AlarmId,AlarmCode,AlarmTypeId,AlarmLevelId,AlarmObjectCode,AlarmStatus,AlarmHandleUser,
AlarmHandleTime,ADDTIME,ParkUserId,BerthCode,BargainOrderCode,BerthStartTime)
VALUES(new.AlarmId,new.AlarmCode,new.AlarmTypeId,new.AlarmLevelId,new.AlarmObjectCode,new.AlarmStatus,new.AlarmHandleUser,
new.AlarmHandleTime,new.ADDTIME,new.ParkUserId,new.BerthCode,new.BargainOrderCode,new.BerthStartTime);
    END;
$$
DELIMITER ;

CREATE  TRIGGER InsertOPM_Alarm_trigger  
 BEFORE  INSERT  ON OPM_Alarm 
 FOR  EACH ROW
BEGIN 
INSERT INTO OPM_Alarm_copy (AlarmId,AlarmCode,AlarmTypeId,AlarmLevelId,AlarmObjectCode,AlarmStatus,AlarmHandleUser,
AlarmHandleTime,ADDTIME,ParkUserId,BerthCode,BargainOrderCode,BerthStartTime)
VALUES(new.AlarmId,new.AlarmCode,new.AlarmTypeId,new.AlarmLevelId,new.AlarmObjectCode,new.AlarmStatus,new.AlarmHandleUser,
new.AlarmHandleTime,new.ADDTIME,new.ParkUserId,new.BerthCode,new.BargainOrderCode,new.BerthStartTime);
END ;

mysql触发器监控mysql数据表记录删除操作

DELIMITER $$

USE `DB_Test`$$

DROP TRIGGER /*!50032 IF EXISTS */ `SYS_OPM_trigger`$$

CREATE
    /*!50017 DEFINER = 'root'@'%' */
    TRIGGER `SYS_OPM_trigger` AFTER DELETE ON `OPM_Alarm` 
    FOR EACH ROW BEGIN
    DECLARE str VARCHAR(40000);
      SET str=CONCAT(old.AlarmId,'@',old.AlarmCode,'@',old.AlarmTypeId,'@',old.AlarmLevelId,'@',
      old.AlarmObjectCode,'@',old.AlarmStatus,'@',old.AlarmHandleUser,'@',old.AlarmHandleTime,'@',
      old.AddTime,'@',old.ParkUserId,'@',old.BerthCode,'@',old.BargainOrderCode,'@',old.BerthStartTime);
      INSERT INTO OPM_AlarmAction_log(UserName,Client_IP,Delete_before_key,Delete_Date) 
    VALUES(SUBSTRING_INDEX(USER(),'@',1),SUBSTRING_INDEX(USER(),'@',-1), str, NOW());
    END;
$$

DELIMITER ;

删除前添加原记录备份到另一记录表

DELIMITER $$

USE `DB_Test`$$

DROP TRIGGER /*!50032 IF EXISTS */ `InsertOPM_Alarm_trigger`$$

CREATE
    /*!50017 DEFINER = 'root'@'%' */
    TRIGGER `InsertOPM_Alarm_trigger` BEFORE 

DELETE ON `OPM_Alarm` 
    FOR EACH ROW BEGIN
     INSERT INTO OPM_Alarm_copy 

(AlarmId,AlarmCode,AlarmTypeId,AlarmLevelId,AlarmObjectCode,AlarmStatus,AlarmHandleUser,
        AlarmHandleTime,ADDTIME,ParkUserId,BerthCode,BargainOrderCode,BerthStartTime)
         VALUES

(old.AlarmId,old.AlarmCode,old.AlarmTypeId,old.AlarmLevelId,old.AlarmObjectCode,old.AlarmS

tatus,old.AlarmHandleUser,
                 

old.AlarmHandleTime,old.ADDTIME,old.ParkUserId,old.BerthCode,old.BargainOrderCode,old.Bert

hStartTime);
         

    END;
$$

DELIMITER ;