Roll back a MySQL transaction

Databases
1

I am trying to catch the error occurred while inserting 2 records in 2 different tables. I want to rollback the already stored query in table1 if the insertion failed in the second table. I have tried to search for the solution, and I decided to use an if condition inside a procedure to check the number of errors using @@error_count. Here is my code:

DROP PROCEDURE IF EXISTS mprocedure;
DELIMITER $$
CREATE PROCEDURE mprocedure ()
BEGIN
START TRANSACTION;
INSERT INTO `mschema`.`table1`
(`maxbudget`,
`blocked`,
`d_percentage`,
`max discount`)
VALUES
('2250',
'0',
'.9',
'.99');
if  (@@error_count = 0 ) then    
    INSERT INTO `mschema`.`table2`
     (`name`,`image`,`date`,`fKey_id`)
     values
         ('jhon','jfdd', '2018-01-01 00:00:00', LAST_INSERT_ID());
      if (@@error_count = 0 ) then 
          commit;
      else 
          rollback;
      end if;
else rollback;
end if;
commit;
END;
$$
DELIMITER ;

Note that the firstdate value is not repetitive in table 2. When I execute it, it executed successfully and gives me the required result.
But when trying to execute it again (to test the rollback), it was saved in the first table and error was generated in the second table due to duplication and unfortunately the rollback did not revert the table 1 saved record. Any help is appreciated.

2

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.