场景描述
当同一个mysql程序中，有两个数据库test_main（主数据库）和test_follow（从数据库），都存在user表，需要对user表进行同步，打开test_main数据库，在里面创建如下触发器：
1、当两张user表字段一样且有id字段时
a、插入同步
CREATE TRIGGER `user_syn_insert` AFTER INSERT ON `user` FOR EACH ROW BEGIN
INSERT INTO test_follow.user SELECT * FROM `user` WHERE `user`.id=new.id;
END
b、更新同步
CREATE TRIGGER `user_syn_update` AFTER UPDATE ON `user` FOR EACH ROW BEGIN
DELETE FROM test_follow.user where `id`=old.id;
INSERT INTO test_follow.user SELECT * FROM `user` WHERE `user`.id=new.id;
END
c、删除同步
CREATE TRIGGER `user_syn_delete` BEFORE DELETE ON `user` FOR EACH ROW BEGIN
DELETE FROM test_follow.student where `id`=old.id;
END
2、当两张user表字段不一样且有id字段时，或只同步指定字段时
a、插入同步
CREATE TRIGGER `user_syn_insert` AFTER INSERT ON `user` FOR EACH ROW BEGIN
INSERT INTO test_follow.user(`id`,`name`,`age`) VALUES(new.id,new.name,new.age);
END
b、更新同步
CREATE TRIGGER `user_syn_update` AFTER UPDATE ON `user` FOR EACH ROW BEGIN
UPDATE test_follow.user SET `name`=new.name,`age`=new.age WHERE`id`=new.id;
END
c、删除同步
CREATE TRIGGER `user_syn_delete` BEFORE DELETE ON `user` FOR EACH ROW BEGIN
DELETE FROM test_follow.student where `id`=old.id;
END
3、对于只有两个字段的简单中间表，是没有id字段的，这时建议统一使用相同的字段名，比如a_id,b_id。（对于有其他字段的中间表，建议给表增设id字段）
a、插入同步
CREATE TRIGGER `ux_syn_insert` AFTER INSERT ON `ux` FOR EACH ROW BEGIN
INSERT INTO test_follow.ux (a_id,b_id) VALUES (new.a_id,new.b_id);
END
b、更新同步
CREATE TRIGGER `ux_syn_update` AFTER UPDATE ON `ux` FOR EACH ROW BEGIN
UPDATE test_follow.ux set a_id=new.a_id,b_id=new.b_id WHERE a_id=old.a_id and b_id=old.b_id;
END
c、删除同步
CREATE TRIGGER `ux_syn_delete` AFTER DELETE ON `ux` FOR EACH ROW BEGIN
DELETE FROM test_follow.ux where a_id=old.a_id and b_id=old.b_id;
END
4、最后，关于删除触发器的sql语句
DROP TRIGGER `user_syn_delete`;
