MySQL触发器实现两表数据同步（增删改详解）

MySQL触发器实现两表数据同步（增删改详解）

因为一个需求需要两个表里面数据同步,在度娘搜索后查到了关于Mysql触发器,写下来分享给大家!

创建一个数据表 order_1，同时复制出一份表order_2

表结构如下：

CREATE TABLE a_order_1 (
order_id int(11) NOT NULL AUTO_INCREMENT,
order_sn varchar(100) DEFAULT NULL,
user_nick varchar(100) DEFAULT NULL,
user_mobile varchar(100) DEFAULT NULL,
address varchar(255) DEFAULT NULL,
status tinyint(1) DEFAULT NULL,
payment decimal(10,2) DEFAULT NULL,
PRIMARY KEY (order_id)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

2. 创建插入触发器

DELIMITER $
create trigger a_order_1_trigger after insert
on a_order_1 for each row
begin
INSERT INTO a_order_2(order_id,order_sn,user_nick,user_mobile,address,status,payment) VALUES(new.order_id,new.order_sn,new.user_nick,new.user_mobile,new.address,new.status,new.payment);
END $;
DELIMITER ;

3. 创建更新触发器

DELIMITER $
create trigger a_order_1_trigger_u after update
on a_order_1 for each row
begin
update a_order_2 set order_id=new.order_id,order_sn=new.order_sn,user_nick=new.user_nick,user_mobile=new.user_mobile,address=new.address,status=new.status,payment=new.payment where order_id=old.order_id;
END$;
DELIMITER ;

4 创建删除触发器

DELIMITER $
create trigger a_order_1_trigger_d after delete
on a_order_1 for each row
begin
delete from a_order_2 where order_id=old.order_id;
END$;
DELIMITER ;

原文是来自于csdn的另外一个老哥的,写得特别清楚!!!

原文转自(https://blog.csdn.net/qq_21891743/article/details/85061495)