请问如何更优雅的设计数据库同步问题?这个示例是说明使用触发器进行同步

Main_csdn_String 2018-07-04 04:32:44
DELIMITER $$ 
USE `a`; $$
DROP TRIGGER IF EXISTS `a`.`aai`; $$
CREATE TRIGGER `a`.`aai`
AFTER INSERT ON `a`.`aa`
FOR EACH ROW
BEGIN
IF (new.`servers` = 'b' AND new.type = 0) THEN
INSERT INTO `c`.`cc` (`uuid`,`type`,`servers`,`name`)VALUES(new.uuid,new.type,new.`servers`,new.`name`);
INSERT INTO `d`.`dd` (`uuid`,`type`,`servers`,`name`)VALUES(new.uuid,new.type,new.`servers`,new.`name`);
END IF;
IF (new.`servers` = 'c' AND new.type = 0) THEN
INSERT INTO `b`.`bb` (`uuid`,`type`,`servers`,`name`)VALUES(new.uuid,new.type,new.`servers`,new.`name`);
INSERT INTO `d`.`dd` (`uuid`,`type`,`servers`,`name`)VALUES(new.uuid,new.type,new.`servers`,new.`name`);
END IF;
IF (new.`servers` = 'd' AND new.type = 0) THEN
INSERT INTO `b`.`bb` (`uuid`,`type`,`servers`,`name`)VALUES(new.uuid,new.type,new.`servers`,new.`name`);
INSERT INTO `c`.`cc` (`uuid`,`type`,`servers`,`name`)VALUES(new.uuid,new.type,new.`servers`,new.`name`);
END IF;
END; $$

DELIMITER $$

USE `a`; $$
DROP TRIGGER IF EXISTS `a`.`aau`; $$
CREATE TRIGGER `a`.`aau` AFTER UPDATE
ON `a`.`aa` FOR EACH ROW
BEGIN
IF (new.`servers` = 'b' AND new.type = 0) THEN
UPDATE `c`.`cc` SET `uuid` = new.uuid, `type` = new.`type`,`servers` = new.servers,`name` = new.name WHERE `uuid` = new.uuid;
UPDATE `d`.`dd` SET `uuid` = new.uuid, `type` = new.`type`,`servers` = new.servers,`name` = new.name WHERE `uuid` = new.uuid;
END IF;
IF (new.`servers` = 'c' AND new.type = 0) THEN
UPDATE `b`.`bb` SET `uuid` = new.uuid, `type` = new.`type`,`servers` = new.servers,`name` = new.name WHERE `uuid` = new.uuid;
UPDATE `d`.`dd` SET `uuid` = new.uuid, `type` = new.`type`,`servers` = new.servers,`name` = new.name WHERE `uuid` = new.uuid;
END IF;
IF (new.`servers` = 'd' AND new.type = 0) THEN
UPDATE `b`.`bb` SET `uuid` = new.uuid, `type` = new.`type`,`servers` = new.servers,`name` = new.name WHERE `uuid` = new.uuid;
UPDATE `c`.`cc` SET `uuid` = new.uuid, `type` = new.`type`,`servers` = new.servers,`name` = new.name WHERE `uuid` = new.uuid;
END IF;
END;
$$


DELIMITER $$
USE `a`; $$
DROP TRIGGER IF EXISTS `a`.`aad`; $$
CREATE TRIGGER `a`.`aad` AFTER DELETE
ON `a`.`aa` FOR EACH ROW
BEGIN
IF (OLD.`servers` = 'b' AND OLD.type = 0) THEN
DELETE FROM `c`.`cc` WHERE `uuid` = OLD.uuid;
DELETE FROM `d`.`dd` WHERE `uuid` = OLD.uuid;
END IF;
IF (OLD.`servers` = 'c' AND OLD.type = 0) THEN
DELETE FROM `b`.`bb` WHERE `uuid` = OLD.uuid;
DELETE FROM `d`.`dd` WHERE `uuid` = OLD.uuid;
END IF;
IF (OLD.`servers` = 'd' AND OLD.type = 0) THEN
DELETE FROM `c`.`cc` WHERE `uuid` = OLD.uuid;
DELETE FROM `d`.`dd` WHERE `uuid` = OLD.uuid;
END IF;
END;
$$

DELIMITER $$
USE `b`; $$
DROP TRIGGER IF EXISTS `b`.`bbi`; $$
CREATE TRIGGER `b`.`bbi`
BEFORE INSERT ON `b`.`bb`
FOR EACH ROW
BEGIN
IF (new.`servers` = 'b') THEN
INSERT INTO `a`.`aa` (`uuid`,`type`,`servers`,`name`)VALUES(new.uuid,new.type,new.`servers`,new.`name`);
END IF;
END; $$

DELIMITER $$
USE `b`; $$
DROP TRIGGER IF EXISTS `b`.`bbd`; $$
CREATE TRIGGER `b`.`bbd` AFTER DELETE
ON `b`.`bb` FOR EACH ROW
BEGIN
IF (OLD.`servers` = 'b') THEN
DELETE FROM `a`.`aa` WHERE `uuid` = OLD.uuid;
END IF;
END;
$$

DELIMITER $$
USE `b`; $$
DROP TRIGGER IF EXISTS `b`.`bbu`; $$
CREATE TRIGGER `b`.`bbu` AFTER UPDATE
ON `b`.`bb` FOR EACH ROW
BEGIN
IF (new.`servers` = 'b') THEN
UPDATE `a`.`aa` SET `uuid` = new.uuid, `servers` = new.servers,`name` = new.name WHERE `uuid` = new.uuid;
END IF;
END;
$$

DELIMITER $$
USE `c`; $$
DROP TRIGGER IF EXISTS `c`.`cci`; $$
CREATE TRIGGER `c`.`cci`
BEFORE INSERT ON `c`.`cc`
FOR EACH ROW
BEGIN
IF (new.`servers` = 'c') THEN
INSERT INTO (`uuid`,`type`,`servers`,`name`)VALUES(new.uuid,new.type,new.`servers`,new.`name`);
END IF;
END; $$


DELIMITER $$
USE `c`; $$
DROP TRIGGER IF EXISTS `c`.`ccd`; $$
CREATE TRIGGER `c`.`ccd` AFTER DELETE
ON `c`.`cc` FOR EACH ROW
BEGIN
IF (OLD.`servers` = 'c') THEN
DELETE FROM `a`.`aa` WHERE `uuid` = OLD.uuid;
END IF;
END;
$$

DELIMITER $$
USE `c`; $$
DROP TRIGGER IF EXISTS `c`.`ccu`; $$
CREATE TRIGGER `c`.`ccu` AFTER UPDATE
ON `c`.`cc` FOR EACH ROW
BEGIN
IF (new.`servers` = 'c') THEN
UPDATE `a`.`aa` SET `uuid` = new.uuid, `servers` = new.servers,`name` = new.name WHERE `uuid` = new.uuid;
END IF;
END;
$$


DELIMITER $$
USE `d`; $$
DROP TRIGGER IF EXISTS `d`.`ddi`; $$
CREATE TRIGGER `d`.`ddi`
BEFORE INSERT ON `d`.`dd`
FOR EACH ROW
BEGIN
IF (new.`servers` = 'd')
THEN
INSERT INTO (`uuid`,`type`,`servers`,`name`)VALUES(new.uuid,new.type,new.`servers`,new.`name`);
END IF;
END; $$

DELIMITER $$
USE `d`; $$
DROP TRIGGER IF EXISTS `d`.`ddd`; $$
CREATE TRIGGER `d`.`ddd` AFTER DELETE
ON `d`.`dd` FOR EACH ROW
BEGIN
IF (OLD.`servers` = 'd') THEN
DELETE FROM `a`.`aa` WHERE `uuid` = OLD.uuid;
END IF;
END;
$$

DELIMITER $$
USE `d`; $$
DROP TRIGGER IF EXISTS `d`.`ddu`; $$
CREATE TRIGGER `d`.`ddu` AFTER UPDATE
ON `d`.`dd` FOR EACH ROW
BEGIN
IF (new.`servers` = 'd') THEN
UPDATE `a`.`aa` SET `uuid` = new.uuid, `servers` = new.servers,`name` = new.name WHERE `uuid` = new.uuid;
END IF;
END;
$$
...全文
216 2 打赏 收藏 转发到动态 举报
写回复
用AI写文章
2 条回复
切换为时间正序
请发表友善的回复…
发表回复
Main_csdn_String 2018-07-05
  • 打赏
  • 举报
回复
引用 1 楼 ACMAIN_CHM 的回复:
 FOR EACH ROW 
BEGIN
IF (new.`servers` = 'b' AND new.type = 0) THEN
INSERT INTO `c`.`cc` (`uuid`,`type`,`servers`,`name`)VALUES(new.uuid,new.type,new.`servers`,new.`name`);
INSERT INTO `d`.`dd` (`uuid`,`type`,`servers`,`name`)VALUES(new.uuid,new.type,new.`servers`,new.`name`);
END IF;
IF (new.`servers` = 'c' AND new.type = 0) THEN
INSERT INTO `b`.`bb` (`uuid`,`type`,`servers`,`name`)VALUES(new.uuid,new.type,new.`servers`,new.`name`);
INSERT INTO `d`.`dd` (`uuid`,`type`,`servers`,`name`)VALUES(new.uuid,new.type,new.`servers`,new.`name`);
END IF;
IF (new.`servers` = 'd' AND new.type = 0) THEN
INSERT INTO `b`.`bb` (`uuid`,`type`,`servers`,`name`)VALUES(new.uuid,new.type,new.`servers`,new.`name`);
INSERT INTO `c`.`cc` (`uuid`,`type`,`servers`,`name`)VALUES(new.uuid,new.type,new.`servers`,new.`name`);
END IF;
END;



FOR EACH ROW
BEGIN
IF new.type = 0 THEN
IF new.`servers` != 'b' THEN
INSERT INTO `b`.`bb` (`uuid`,`type`,`servers`,`name`)VALUES(new.uuid,new.type,new.`servers`,new.`name`);
END IF;
IF new.`servers` != 'c' THEN
INSERT INTO `c`.`cc` (`uuid`,`type`,`servers`,`name`)VALUES(new.uuid,new.type,new.`servers`,new.`name`);
END IF;
IF new.`servers` != 'd' THEN
INSERT INTO `d`.`dd` (`uuid`,`type`,`servers`,`name`)VALUES(new.uuid,new.type,new.`servers`,new.`name`);
END IF;
END IF;
END;
挺好-
ACMAIN_CHM 2018-07-04
  • 打赏
  • 举报
回复
 FOR EACH ROW 
BEGIN
IF (new.`servers` = 'b' AND new.type = 0) THEN
INSERT INTO `c`.`cc` (`uuid`,`type`,`servers`,`name`)VALUES(new.uuid,new.type,new.`servers`,new.`name`);
INSERT INTO `d`.`dd` (`uuid`,`type`,`servers`,`name`)VALUES(new.uuid,new.type,new.`servers`,new.`name`);
END IF;
IF (new.`servers` = 'c' AND new.type = 0) THEN
INSERT INTO `b`.`bb` (`uuid`,`type`,`servers`,`name`)VALUES(new.uuid,new.type,new.`servers`,new.`name`);
INSERT INTO `d`.`dd` (`uuid`,`type`,`servers`,`name`)VALUES(new.uuid,new.type,new.`servers`,new.`name`);
END IF;
IF (new.`servers` = 'd' AND new.type = 0) THEN
INSERT INTO `b`.`bb` (`uuid`,`type`,`servers`,`name`)VALUES(new.uuid,new.type,new.`servers`,new.`name`);
INSERT INTO `c`.`cc` (`uuid`,`type`,`servers`,`name`)VALUES(new.uuid,new.type,new.`servers`,new.`name`);
END IF;
END;



FOR EACH ROW
BEGIN
IF new.type = 0 THEN
IF new.`servers` != 'b' THEN
INSERT INTO `b`.`bb` (`uuid`,`type`,`servers`,`name`)VALUES(new.uuid,new.type,new.`servers`,new.`name`);
END IF;
IF new.`servers` != 'c' THEN
INSERT INTO `c`.`cc` (`uuid`,`type`,`servers`,`name`)VALUES(new.uuid,new.type,new.`servers`,new.`name`);
END IF;
IF new.`servers` != 'd' THEN
INSERT INTO `d`.`dd` (`uuid`,`type`,`servers`,`name`)VALUES(new.uuid,new.type,new.`servers`,new.`name`);
END IF;
END IF;
END;

56,675

社区成员

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧