利用触发器同步两张表的数据

insert 和update

触发器不会产生new表和old表，所谓new，old只是指 insert 和 delete

 

对于INSERT语句, 只有NEW是合法的；

对于DELETE语句，只有OLD才合法；

对于UPDATE语句，NEW、OLD可以同时使用。

需求：更新和插入 insert_event_process 表，要同步更新和插入 insert_event_process_his 表

#利用触发器，同步onm_event_process的insert操作

DELIMITER ||
CREATE TRIGGER insert_event_process
AFTER INSERT ON onm_event_process
FOR EACH ROW
BEGIN

INSERT INTO 
onm_event_process_his(`uuid`, `event_id`, `node_id`, `operate`, `message`, `operator_id`, `operator_name`, `receiver`, `start_time`, `end_time`) VALUES
(NEW.`uuid`, NEW.`event_id`, NEW.`node_id`, NEW.`operate`, NEW.`message`, NEW.`operator_id`, NEW.`operator_name`, NEW.`receiver`, NEW.`start_time`, NEW.`end_time`);

END||
DELIMITER ;

#利用触发器，同步onm_event_process的update操作

DELIMITER ||
CREATE TRIGGER update_event_process
AFTER UPDATE ON onm_event_process
FOR EACH ROW
BEGIN

UPDATE onm_event_process_his 
SET `uuid` = NEW.`uuid`, `event_id` = NEW.`event_id`, `node_id`= NEW.`node_id`, `operate` = NEW.`operate`, `message` =NEW.`message`, `operator_id` =NEW.`operator_id`, `operator_name` = NEW.`operator_name`, `receiver` = NEW.`receiver`, `start_time` = NEW.`start_time`, `end_time` =NEW.`end_time` WHERE `uuid` = NEW.`uuid`;

END||
DELIMITER ;

#删除插入触犯器
DROP TRIGGER  IF EXISTS insert_event_process

#删除更新触犯器
DROP TRIGGER  IF EXISTS insert_event_process