MySQL Chained Triggers

Question

If I have two tables A and B both with insert triggers on them.

If the table A trigger inserts a row into B then performs an update when would the B trigger fire? Immediately on the insert or would it wait for the A trigger to complete?

using pseudo pseudo code would it be

A Trigger Starts
  A Trigger Inserts Row into B
  A Trigger Updates B
A Trigger Ends

B Trigger Fires (after insert)

or

A Trigger Starts
  A Trigger Inserts Row into B
  B Trigger Fires (after insert)
  A Trigger Updates B
A Trigger Ends

Answer 1

Well you could've checked it yourself using a log table (general_log won't help you with this) with code like this

Tables schema

CREATE TABLE a (id INT);
CREATE TABLE b (id INT);
CREATE TABLE c (id INT);
DROP TABLE IF EXISTS tg_log;
CREATE TABLE tg_log 
(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  dt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  message VARCHAR(512)
);

Triggers

DROP TRIGGER IF EXISTS tg_a;
DELIMITER $$
CREATE TRIGGER tg_a
AFTER INSERT ON a
FOR EACH ROW
BEGIN
  INSERT INTO tg_log(message) VALUES('tg_a START');
  INSERT INTO b VALUES (NEW.id);
  INSERT INTO tg_log(message) VALUES('tg_a INSERT INTO b');
  UPDATE b SET id = id + 1 WHERE id = NEW.id;
  INSERT INTO tg_log(message) VALUES('tg_a UPDATE b');
  INSERT INTO tg_log(message) VALUES('tg_a END');
END$$
DELIMITER ;

DROP TRIGGER IF EXISTS tg_b;
DELIMITER $$  
CREATE TRIGGER tg_b
AFTER INSERT ON b
FOR EACH ROW
BEGIN
  INSERT INTO tg_log(message) VALUES('tg_b START');
  INSERT INTO c VALUES (NEW.id);
  INSERT INTO tg_log(message) VALUES('tg_b INSERT INTO c');
  INSERT INTO tg_log(message) VALUES('tg_b END');
END$$
DELIMITER ;

Then we insert a row into table a

INSERT INTO a VALUES(1);

And here is what we see in out log

+----+---------------------+--------------------+
| id | dt                  | message            |
+----+---------------------+--------------------+
|  1 | 2013-07-27 23:31:42 | tg_a START         |
|  2 | 2013-07-27 23:31:42 | tg_b START         |
|  3 | 2013-07-27 23:31:42 | tg_b INSERT INTO c |
|  4 | 2013-07-27 23:31:42 | tg_b END           |
|  5 | 2013-07-27 23:31:42 | tg_a INSERT INTO b |
|  6 | 2013-07-27 23:31:42 | tg_a UPDATE b      |
|  7 | 2013-07-27 23:31:42 | tg_a END           |
+----+---------------------+--------------------+
7 rows in set (0.00 sec)

Here is SQLFiddle demo