MySQL transaction and trigger

Question

I quickly browsed MySQL manual but didn't find the exact information about my question. Here is my question: if I have a InnoDB table A with two triggers triggered by 'AFTER INSERT ON A' and 'AFTER UPDATE ON A'. More specifically, For example: one trigger is defined as:

CREATE TRIGGER test_trigger AFTER INSERT ON A
         FOR EACH ROW 
                      BEGIN
                         INSERT INTO B SELECT * FROM A WHERE A.col1 = NEW.col1
                      END;

You can ignore the query between BEGIN AND END, basically I mean this trigger will insert several rows into table B which is also a InnoDB table.

Now, if I started a transaction and then insert many rows, say: 10K rows, into table A. If there is no trigger associated with table A, all these inserts are atomic, that's for sure. Now, if table A is associated with several insert/update triggers which insert/update many rows to table B and/or table C etc.. will all these inserts and/or updates are still all atomic?

I think it's still atomic, but it's kind of difficult to test and I can't find any explanations in the Manual. Anyone can confirm this?

Answer 1

And by atomic, you mean if one statement in the trigger fails, the whole statement fails. Yes -- the trigger is done in the context of statement's transaction. And no, of course, if there is no transaction, then there is no transaction context.

For transactional tables, failure of a statement should cause rollback of all changes performed by the statement. Failure of a trigger causes the statement to fail, so trigger failure also causes rollback. For nontransactional tables, such rollback cannot be done, so although the statement fails, any changes performed prior to the point of the error remain in effect.

And you aren't allowed to start a transaction in the trigger.

Answer 2

They should be atomic, if done in a transaction. The MySQL page on triggers has the appropriate information.

http://dev.mysql.com/doc/refman/5.0/en/triggers.html