Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How can we perform ROLLBACK transactions inside a MySQL stored procedure?
As we know that ROLLBACK will revert any changes made to the database after the transaction has been started. To perform the ROLLBACK in MySQL stored procedure we must have to declare EXIT handler. We can use a handler for either sqlexception or SQL warnings. It can be understood with the help of an example in which stored procedure having ROLLBACK created for the table having the following details −
mysql> SHOW CREATE table gg\G *************************** 1. row *************************** Table: gg Create Table: CREATE TABLE `gg` ( `Id` int(11) NOT NULL AUTO_INCREMENT, `Name` varchar(30) NOT NULL, PRIMARY KEY (`Id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 1 row in set (0.00 sec)
We can see that the column ‘name’ cannot have NULL values and table have the following data −
mysql> Select * from employee.tbl; +----+---------+ | Id | Name | +----+---------+ | 1 | Mohan | | 2 | Gaurav | | 3 | Sohan | | 4 | Saurabh | | 5 | Yash | | 6 | Rahul | +----+---------+ 6 rows in set (0.00 sec)
Example
mysql> Delimiter // mysql> Create Procedure st_transaction_Rollback() -> BEGIN -> DECLARE exit handler for sqlexception -> BEGIN -> ROLLBACK; -> END; -> DECLARE exit handler for sqlwarning -> BEGIN -> ROLLBACK; -> END; -> START TRANSACTION; -> INSERT INTO employee.tbl(name) values(); -> UPDATE employee.tbl set name = 'YashPal' where id = 5; -> COMMIT; -> END // Query OK, 0 rows affected (0.02 sec) mysql> Delimiter ; mysql> CALL st_transaction_Rollback () Query OK, 0 rows affected (0.00 sec) mysql> Select * from employee.tbl; +----+---------+ | Id | Name | +----+---------+ | 1 | Mohan | | 2 | Gaurav | | 3 | Sohan | | 4 | Saurabh | | 5 | Yash | | 6 | Rahul | +----+---------+ 6 rows in set (0.00 sec)
We can see from the above result set that the changes made by UPDATE statement have been ROLLBACKED because the first query of INSERT has thrown an error (trying to insert NULL values).
Updated on: 22-Jun-2020
2K+ Views
Advertisements