Just that is the question: is possible to do a ROLLBACK in a MySQL trigger?
If answer is yes, then, please, explain how.
If a ROLLBACK TRANSACTION is issued in a trigger: All data modifications made to that point in the current transaction are rolled back, including any made by the trigger. The trigger continues executing any remaining statements after the ROLLBACK statement.
A COMMIT or ROLLBACK statement ends the current transaction and a new one starts. If a session that has autocommit disabled ends without explicitly committing the final transaction, MySQL rolls back that transaction.
When the rollback trigger is executed, Adaptive Server aborts the currently executing command and halts execution of the rest of the trigger.
To have possibility to ROLLBACK DML statements (like INSERT , UPDATE or DELETE queries) you should use transaction blocks: START TRANSACTION; UPDATE CUSTOMERS SET ADDRESS = 'Pune' WHERE ID = 6; -- and more DML queries COMMIT; -- or ROLLBACK; Since transaction was COMMIT ed it can not be rolled back.
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 ;
If the trigger raises an exception, that will abort the transaction, effectively rolling back. Will this work for you?
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With