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