MySQL – How to Handle Rollback and Triggers

MySQLrollbacktrigger

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

Best Answer

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

Related Question