I have a MySQL stored procedure. Cut down, it looks like this.
START TRANSACTION
SELECT some_columns
SET some_variables
UPDATE row_in_balance_table
UPDATE row_in_entry_table
INSERT row_in_ledger_table
INSERT row_in_ledger_table
COMMIT;
I need all 4 rows to be updated/inserted, or none of them to be.
What is just a standard normal way to make this happen? I had considered something like, after each query
IF (SELECT ROW_COUNT() = 1 ) THEN
SET row_affected_counter = row_affected_counter + 1;
END IF;
And then, because I need to affect 4 total rows, just before the COMMIT I could use..
IF (row_affected_counter != 4 ) THEN
ROLLBACK;
END IF;
COMMIT;
So I think my procedure should rollback on an error, because it's in a transaction, and rollback if any of the updates/inserts don't happen, because the counter won't reach the expected total of rows affected.
This didn't work though because it seems like ROW_COUNT doesn't reset to 0 if a follow insert/update is called.
Is there a better way to do this?
