Mysql rollback and triggers

Question

How does mysql triggers and rollback working together? I need to know how this is working together? I can rollback if i proceed with more than one sql statement because it is in same query but it seems different with triggers because it proceeds seperately

If my sql statement fails and raises an error then trigger still proceeds? If it does how can i stop trigger?

And if my sql statement was successfull and trigger raised and error, how will i know and rollback?

Thanks

Answer 1

To provide examples...

Case 1:

 SET autocommit=ON;
 INSERT ...; -- with or without triggers
 UPDATE ...; -- with or without triggers

The INSERT and its trigger actions are performed atomically. That is, the INSERT and trigger are either completely done or completely undone.
The UPDATE and its trigger actions are performed atomically. That is, the UPDATE and trigger are either completely done or completely undone.

Case 2:

 -- (the value of `autocommit` does not matter)
 START TRANSACTION;
 INSERT ...; -- with or without triggers
 UPDATE ...; -- with or without triggers
 if ... then ROLLBACK else COMMIT;

The INSERT (and its trigger actions) might succeed or might fail and be rolled back.
The UPDATE (and its trigger actions) might succeed or might fail and be rolled back.
You might choose to ROLLBACK -- In this case all of the above stuff would be undone.
You might choose to COMMIT -- In this case all of the above stuff that has not already been rolled back would be committed.

Case 3: (Do not use this case; it is too user-error-prone.)

 SET autocommit = OFF;
 INSERT ...; -- with or without triggers
 UPDATE ...; -- with or without triggers
 if ... then ROLLBACK else COMMIT;

This works the same as Case 2, except there is some question about the scope of the ROLLBACK/COMMIT.

(Please edit this answer if it is not correct.)

• Michael - sqlbot: Seems legit. I'm not aware of any anomalous behavior when autocommit is off... as far as I have ever known, COMMIT and ROLLBACK become equivalent to COMMIT AND CHAIN and ROLLBACK AND CHAIN when autocommit is disabled -- that is, every explicit commit or rollback are followed by an implicit START TRANSACTION... but it's possible.
• Rick James: @Michael-sqlbot - I don't know whether SET autocommit=OFF; INSERT..; SET autocommit=OFF; UPDATE..; COMMIT; is 1 or 2 transactions.
• Michael - sqlbot: Okay, now that is very possibly 2 transactions.
• Rick James: My main gripe is forgetting to ever issue COMMIT.

Answer 2

What happens in case of an error is described in the documentation:

MySQL handles errors during trigger execution as follows:

• If a BEFORE trigger fails, the operation on the corresponding row is not performed.

• A BEFORE trigger is activated by the attempt to insert or modify the row, regardless of whether the attempt subsequently succeeds.

• An AFTER trigger is executed only if any BEFORE triggers and the row operation execute successfully.

• An error during either a BEFORE or AFTER trigger results in failure of the entire statement that caused trigger invocation.

• 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.

So assuming you are using InnoDB, the statement and the triggers (and also any trigger that might be invoked by changes made by the triggers themselves) are atomic. If one fails, the other fails too and gets rolled back. This doesn't require an explicit transactions.

If you are using e.g. MyISAM (which doesn't support transactions) and a failure occurs, the changes made by any previous step in the list above cannot be rolled back. This will also happen if e.g. a trigger on an InnoDB-table inserts a row into a MyISAM table. If a failure occurs in any later step, only the changes to the involved InnoDB-tables will get rolled back.

• user120047: Thanks solarflare, i use innodb, so if i start a sql statement then one transaction at the statement will be enough, even statement has no errors if the trigger has an error the statement will return error and will execute the rollback? Did i get it right?
• Solarflare: You don't need a transaction for that. A single query will always be atomic (= you can treat it as one single impartible thing, that either does everything it has to do, or does nothing), including every trigger it invokes, and, same logic, independent of how many rows a single query updates/inserts. But only this single query (including triggers, ...) will be rolled back when an error occurs. If you start a transaction, you have to take care that other queries that maybe came before that get rolled back.
• user120047: but if i dont start transaction how will i be able to rollback? If i dont start a transaction wont mysql assume that it is autocommit?
• Solarflare: YOU do not have to rollback the single query. MySQL will automatically "rollback" the SINGLE query if it (or the trigger) fails. It will not rollback (or end) a transaction if you started it, just the single query. If the query fails, the transaction you started will still be open and you could, if you wanted to, commit it - but the changes of the single failed query will not be commited. Maybe don't think of it as a transaction and rollback: a query is one command that either completely works (including all triggers) or doesn't work. Maybe add an example that you are struggling with.
• Michael - sqlbot: Yes. A trigger doesn't technically run before or after the statement -- it runs during the statement, but before of after the table data itself is modified. After any DML statement returns an error, triggers or not, you can rollback the transaction. After the error is thrown, you are still in your transaction, but the state of the data should be as if that single statement had not been run, as indicated in the docs mentioned in the answer.