添加记录到新记录表

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 ;

mysql 触发器实现两个表的数据同步
作者：字体：[增加减小] 类型：转载
本文将介绍mysql 触发器实现两个表的数据同步，需要的朋友可以参考
mysql通过触发器实现两个表的同步
目前，在本地测试成功。
假设本地的两个数据库a和b，a下有表table1（id, val） b下有表table2（id, val）
假设希望当table1中数据更新，table2中数据同步更新。
代码：
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
TRIGGER `a`.`触发器名` BEFORE UPDATE
ON `a`.`table1`
FOR EACH ROW BEGIN
IF new.id!=old.id THEN
UPDATE `b`.`table2` SET `b`.`table2`.id=new.id WHERE `b`.`table2`.val=old.val;
END IF;
END$$
DELIMITER ;
网上找了很多代码，在phpadmin中运行都不成功，总是语法错误，phpmyadmin也不能可视化建立触发器，所以找了另一个mysql管理工具，SQLyog。这个工具比较好，java写的，可以通过界面创建触发器，然后再把代码拷到phpmyadmin中运行，成功！

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