The mysql trigger synchronizes the data between the two tables
- 2020-05-14 05:07:38
- OfStack
mysql synchronizes the two tables through triggers
So far, it has been successfully tested locally.
Let's say we have two local databases a and b, a has the table table1 (id, val), b has the table table2 (id, val)
Suppose you want to update the data in table1 and the data in table2 simultaneously.
Code:
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
TRIGGER 'a'. 'trigger name' 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 ;
I found a lot of code on the Internet, but it was not successful in phpadmin, because it was always syntactically wrong. Besides, phpmyadmin could not create triggers visually, so I found another mysql management tool, SQLyog. This tool is better, written by java, can create triggers through the interface, and then copy the code to phpmyadmin to run, successful!
So far, it has been successfully tested locally.
Let's say we have two local databases a and b, a has the table table1 (id, val), b has the table table2 (id, val)
Suppose you want to update the data in table1 and the data in table2 simultaneously.
Code:
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
TRIGGER 'a'. 'trigger name' 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 ;
I found a lot of code on the Internet, but it was not successful in phpadmin, because it was always syntactically wrong. Besides, phpmyadmin could not create triggers visually, so I found another mysql management tool, SQLyog. This tool is better, written by java, can create triggers through the interface, and then copy the code to phpmyadmin to run, successful!