title: Mirror tables: triggers, deadlock and implicit commits
body: <p>I'm have 2 similar tables, for example A and B. <strong>I want to replicate insertions in A to B, and insertions in B to A to integrate two users systems</strong> . I configured "after insert triggers" on each one. Example:</p>
  
  <pre><code>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 ;
  </code></pre>
  
  <p>If I insert in A, the triggers calls an insert in B, but this insert executes the trigger in B and a deadlock occurs avoiding a infinite loop.</p>
  
  <p>I've tried to edit triggers to DROP the another table trigger before do the INSERT and then CREATE it again after it. Example:</p>
  
  <pre><code>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 ;
  </code></pre>
  
  <p>However CREATE is a Data Definition Language (DDL) statement that makes an implicit commit. Thus, this can't be done.</p>
  
  <p>I've tried to call a PROCEDURE with the DROP inside to handle explicitly the commit, but isn't possible too.</p>
  
  <p><strong>Any suggestion to mirror this 2 tables?</strong></p>
  
  <hr>
  
  <p>UPDATE: Using <a href="https://stackoverflow.com/users/20860/bill-karwin">Bill Karwin</a> <a href="https://stackoverflow.com/questions/26220377/mirror-tables-triggers-deadlock-and-implicit-commits/26220565#26220565">suggestion</a>, I added a <code>origin</code> field to each table with a respective default vale <code>A</code> or <code>B</code>. Then, I alter (DROP and reCREATE) the triggers as follows:</p>
  
  <p>Trigger in A:</p>
  
  <pre><code>...
  BEGIN 
  IF NEW.`origin`='A' THEN
      INSERT INTO `B`
          SET `id` = NEW.`id`, `name` = NEW.`name`,  `origin` = NEW.`origin`;
      END IF;
  END
  </code></pre>
  
  <p>Trigger in B:</p>
  
  <pre><code>...
  BEGIN 
  IF NEW.`origin`='B' THEN
      INSERT INTO `A`
          SET `id` = NEW.`id`, `name` = NEW.`name`, `origin` = NEW.`origin`;
      END IF;
  END
  </code></pre>