mysql transaction rollback on error

I tried to create a transaction inside stored Procedure in MYSQL that will roll back if it did not insert a row (basically an error occurred) but I could not find anything. Below is a working example that rolls back the first insert operation if there is an error in the second Query. Note that the order of various declaration is important, esp DECLARE Exist .. should be declared right before start Transaction.

DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertUser`(IN `name` VARCHAR(20), IN `email` VARCHAR(30), IN `password` VARCHAR(30))
BEGIN 

DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;

START transaction;

INSERT INTO USER( username, email, 
PASSWORD ) 
VALUES (
name,  email,  '1234'
);

SET @last_id = LAST_INSERT_ID();

INSERT INTO Week(user_id,company_id,year) VALUES(@last_id,1,1);
select Row_Count();

COMMIT;

END;;
DELIMITER ;

By default this stored procedure does not return any result (which may be needed in program eg. PHP). I have used ROW_COUNT() function that returns if a row was insert in the last SQL command. If it returns 1 that means successful transaction, if 0 or empty that means some kind of error.

This entry was posted in Uncategorized. Bookmark the permalink.

1 Response to mysql transaction rollback on error

  1. David Beroff says:

    Thank you!! I’ve been searching for hours for this solution, and your approach is so simple and elegant. Thanks again!

Leave a Reply to David Beroff Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s