MySQL – Capturing Errors in a Transaction and Rollback

MySQL

Trying to craft a transaction from the code to send to MySQL. 

START TRANSACTION

INSERT INTO EMPLOYEE (`firstname`,`lastname`)
 VALUES('john','Doe');

SET EMP_ID = last_insert_id();
 //do some inserts here that fail

COMMIT;

Now, when the other inserts into child tables fail I see that just the first insert being committed. TO avoid that I need to capture error and set rollback on error being triggered. When I include the declare statements for error it just fails saying

"Declare is not valid at this position. expecting EOF ;" Any pointers would be greatly appreciated 

START TRANSACTION

DECLARE error BOOL DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;

....other code

Best Answer

DECLAREs must be placed before all executable code in a BEGIN...END block. `START TRANSACTION is 'executable'.

So...

DELIMITER //    
CREATE ...
BEGIN
    DECLARE _rollback DEFAULT 0;
    START TRANSACTION;
    ... -- other executables
    IF _rollback = 0
    THEN  COMMIT;
    ELSE  ROLLBACK;
    END IF;
END;
//
DELIMITER ;

You can also have nested BEGIN..END blocks. But the declarations in your example should probably go at the beginning of the stored routine. This is because BEGIN..END blocks provide "scoping" for variables such as _rollback.

Related Question