ensure that all records in Stored Procedures is excute fine-CodePudding

please how to ensure that all records in this stored Prodcedures is execute fine, and if insert is not working good dont delete anything .

my Procedure like : i have to table data and data_archive and i want to insert data into data_archive and after this operation delete my data from table data

CREATE DEFINER=`Mybase`@`%` PROCEDURE `archive`()
BEGIN
INSERT `data_archive`
SELECT * FROM `data`;
DELETE FROM `data`;
END

this procedure is working fine but i want to ensure dont delete if if the insert did not work thank you for helps

CodePudding user response：

CREATE DEFINER=`Mybase`@`%` PROCEDURE `archive`()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
    END;
    START TRANSACTION;
        INSERT `data_archive` SELECT * FROM `data`;
        DELETE FROM `data`;
    COMMIT;
END

If both statements are executed successfully then the changes are committed.

If any error occures then the handler rollbacks all changes.

You may add SIGNAL or RESIGNAL statement into the handler for to report to the application that the operation fails.