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;
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?