CODEWITHSUNDEEP
mysqltriggers
Ivan
Ivan

Reputation: 15932

Is possible to do a ROLLBACK in a MySQL trigger?

Just that is the question: is possible to do a ROLLBACK in a MySQL trigger?

If answer is yes, then, please, explain how.

Upvotes: 5

Views: 18767

Answers (3)

BartmanDilaw
BartmanDilaw

Reputation: 415

I've found out that this functionnality exists since MySQL 5.5 and does not work in earlier releases.

The trigger does no rollback or commit. To initiate any rollback, you have to raise an exception. Thus your insert/update/delete command will abort. The rollback or commit action has to be raised around your SQL command.

To raise your exception, in your XXX's trigger (eg.) :

create trigger Trigger_XXX_BeforeInsert before insert on XXX
for each row begin

    if [Test]
    then

      SIGNAL sqlstate '45001' set message_text = "No way ! You cannot do this !";

    end if ;

end ;

Upvotes: 13

Mchl
Mchl

Reputation: 62395

From: http://dev.mysql.com/doc/refman/5.1/en/trigger-syntax.html

The trigger cannot use statements that explicitly or implicitly begin or end a transaction such as START TRANSACTION, COMMIT, or ROLLBACK.

and

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.

Upvotes: 5

Jonathan Hall
Jonathan Hall

Reputation: 79674

If the trigger raises an exception, that will abort the transaction, effectively rolling back. Will this work for you?

Upvotes: 8

Related Questions