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.