How to write fail safe stored procedures

We normally write stored procedures for nearly any transaction that has more than one component to it. For example, creating an invoice record that has many lines would require you to insert a record into a header table, and multiple records into a detail table. But what happens if you insert the header and one of the detail records fails to get inserted properly. In these cases, you’re usually greeted by a SQL Exception and error out. However, depending on how you write your stored procedure, you might now have one header record and some of the detail records in the database. In some cases this might be okay, but in most cases it is not and the preference would be to avoid inserting any of the records if even one of them fails. Luckily MySQL provides a really great solution for this problem.

You simply define the action to be taken once the exception is encountered, in our case a rollback, and then you start and end your transaction in between a “START TRANSACTION” and “END” directive as shown below:

BEGIN

    .. Declare statements ..

    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN

          ROLLBACK;
    END;

    START TRANSACTION;

        .. INSERT  ..
        .. UPDATE  ..
        .. SELECT  ..

    COMMIT;
END