CODEWITHSUNDEEP
mysqlstored-procedurestransactionsrollback
Faiyaz Alam
Faiyaz Alam

Reputation: 1217

Rollback without START TRANSACTION statement is not working in MySQL Stored Procedure

I am using mysql v5.6.

After inserting duplicate record in users table through stored procedure It throws an exception and this is good but unable to rollback for table POSTS.

Here is SQL codes for SP : 

 DELIMITER //
 CREATE PROCEDURE usp_add_user_tests
  ( IN `i_name` VARCHAR(50), 
  IN `i_email` VARCHAR(100), 
  IN `i_status` TINYINT(1) UNSIGNED, 
  OUT `p_sqlcode` INT(11) UNSIGNED, 
  OUT `p_status_message` VARCHAR(100) 
 ) 
 MODIFIES SQL DATA 

 BEGIN 
 DECLARE duplicate_key CONDITION FOR 1062; 
 DECLARE EXIT HANDLER FOR duplicate_key 
 BEGIN SET p_sqlcode=1062; SET p_status_message='Duplicate key error';
ROLLBACK ; 
 END; 

 SET p_sqlcode=0; 

 INSERT INTO posts (title) 
 VALUES('test'); 

 INSERT INTO users (name,email,status) 
 VALUES(i_name,i_email,i_status); 

 IF p_sqlcode<>0 THEN 
 SET p_status_message=CONCAT(p_status_message,' when inserting new user'); 
 ELSE
 SET p_status_message='Success'; 
 END IF; 

 END //
DELIMITER ;

Is it possible to rollback for table posts without using Start Transaction statement.

Upvotes: 0

Views: 1117

Answers (1)

7 Reeds
7 Reeds

Reputation: 2539

I don't think you have actually started a "transaction". I am pretty sure that you need to implement one of the mechanisms listed here: https://dev.mysql.com/doc/refman/5.7/en/commit.html

Upvotes: 3

Related Questions