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

添加记录到新记录表

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 ;