Mirror tables: triggers, dead ends, and implicit commits
I have 2 similar tables, for example A and B. I want to replicate inserts to B and inserts to B to integrate two user systems . I configured "after insert triggers" on each one. Example:
DELIMITER $$
CREATE DEFINER = `root`@`localhost` TRIGGER
`after_A_INSERT`
AFTER INSERT ON `A`
FOR EACH ROW BEGIN
INSERT INTO `B`
SET `id` = NEW.`id`,`name` = NEW.`name`;
END$$
DELIMITER ;
DELIMITER $$
CREATE DEFINER = `root`@`localhost` TRIGGER
`after_B_INSERT`
AFTER INSERT ON `B`
FOR EACH ROW BEGIN
INSERT INTO `A`
SET `id` = NEW.`id`,`name` = NEW.`name`;
END$$
DELIMITER ;
If I insert into A, triggers cause an insert into B, but that insert fires a trigger on B and deadlock occurs, avoiding an infinite loop.
I tried to edit the triggers to DROP another table trigger before doing the INSERT and then CREATE it again after it. Example:
DELIMITER $$
CREATE DEFINER = `root`@`localhost` TRIGGER
`after_B_INSERT`
AFTER INSERT ON `B`
FOR EACH ROW BEGIN
DROP TRIGGER IF EXISTS `after_A_INSERT`;
INSERT INTO `A`
SET `id` = NEW.`id`, `name` = NEW.`name`;
/* And CREATE again here */
END$$
DELIMITER ;
However, CREATE is a Data Definition Language (DDL) statement that makes an implicit commit. So this is not possible.
I tried calling PROCEDURE with DROP inside to handle commit explicitly, but it is also not possible.
Any suggestion for mirroring these two tables?
UPDATE: Using Bill Karwin , I added a field origin
for each table using the appropriate default vale A
or B
. Then I change (DROP and reCREATE) triggers like this:
Trigger in A:
...
BEGIN
IF NEW.`origin`='A' THEN
INSERT INTO `B`
SET `id` = NEW.`id`, `name` = NEW.`name`, `origin` = NEW.`origin`;
END IF;
END
Trigger in B:
...
BEGIN
IF NEW.`origin`='B' THEN
INSERT INTO `A`
SET `id` = NEW.`id`, `name` = NEW.`name`, `origin` = NEW.`origin`;
END IF;
END
source to share
You need to avoid creating a loop somehow.
I would suggest adding a column origin
to both tables. In table A, do DEFAULT 'A'
. In table B, do DEFAULT 'B'
.
When inserting into any of the tables in your application, always omit the column origin
, allowing it to accept the default.
In both triggers, replicates to another table only if the value NEW.origin
is equal to the corresponding default table.
Repeat your comment and new error:
Sorry I forgot to mention that in the trigger when you insert into another table, you must also copy the value NEW.origin
. Just in your application, when you do the original insert, you omit origin
.
Trigger in A:
...
BEGIN
IF NEW.`origin`='A' THEN
INSERT INTO `B`
SET `id` = NEW.`id`, `name` = NEW.`name`, `origin` = NEW.`origin`;
END IF;
END
Trigger in B:
...
BEGIN
IF NEW.`origin`='B' THEN
INSERT INTO `A`
SET `id` = NEW.`id`, `name` = NEW.`name`, `origin` = NEW.`origin`;
END IF;
END
I created these triggers and then tested:
mysql> insert into A set name = 'bill';
Query OK, 1 row affected (0.00 sec)
mysql> insert into B set name = 'john';
Query OK, 1 row affected (0.01 sec)
mysql> select * from A;
+----+------+--------+
| id | name | origin |
+----+------+--------+
| 1 | bill | A |
| 2 | john | B |
+----+------+--------+
2 rows in set (0.00 sec)
mysql> select * from B;
+----+------+--------+
| id | name | origin |
+----+------+--------+
| 1 | bill | A |
| 2 | john | B |
+----+------+--------+
source to share