How to surround different entries in a SQL transaction

PHPMaker General Discussion (PHPMaker)
1

I have a case where a single action (e.g. joining our organisation) can result in an entry in each of two tables (e.g. MEMBER table and BILLING table). NOT master/detail though, of course, there are similarities.

My issue today is that I want to surround the two table insert operations in one transaction. The problem is I can’t see anywhere I can put the rollback. Here is an example in some simple pseudo code:

Try 1 (put BILLING insert in MEMBER Row_Inserting:

Row_Inserting(MEMBER)
  BeginTransaction()
  table(BILLING)->insert
  If BILLING insert fails
    RollbackTransaction() and return false (no Row_Inserted(MEMBER))

Row_Inserted(MEMBER)
  CommitTransaction()

No good. If MEMBER insert fails, I never close the transaction

Try 2 (put BILLING insert in MEMBER Row_Inserted:

Row_Inserted(MEMBER)
  BeginTransaction()
  table(BILLING)->insert
  If BILLING insert fails
    RollbackTransaction() and return false
  else
    CommitTransaction()

No good. If BILLING insert fails, I have no way to rollback the MEMBER transaction.

The fundamental problem is that we don’t have a callback for Row_InsertFailed() to tidy up.

So anyone have any great ideas?