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?
Transactions are for grouping actions into one unit of work which either succeeds or fails in its entirety! Triggers are (normally) for performing (procdural/business logic) code when an event (normally an INSERT , UPDATE or DELETE on a particular table) occurs in the database.
In all cases, a trigger is executed as part of the same transaction as the statement that triggered it, so if either the statement or the trigger causes an error, the effects of both will be rolled back.
A transaction is a sequential group of database manipulation operations, which is performed as if it were one single work unit. In other words, a transaction will never be complete unless each individual operation within the group is successful.
A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table. Some uses for triggers are to perform checks of values to be inserted into a table or to perform calculations on values involved in an update.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With