Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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
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.)
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.
Mar 30, 2017 at 1:38
SET autocommit=OFF; INSERT..; SET autocommit=OFF; UPDATE..; COMMIT; is 1 or 2 transactions.
Mar 30, 2017 at 2:57
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.
