Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL transaction and trigger

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?

like image 673
WilliamLou Avatar asked Apr 14 '10 03:04

WilliamLou


People also ask

Is a trigger a transaction?

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.

Do triggers run in transaction?

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.

What is a transaction in MySQL?

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.

What is a triggers in MySQL?

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.


1 Answers

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.

like image 195
Gerard ONeill Avatar answered Oct 23 '22 19:10

Gerard ONeill