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.
If your database or table engine supports transaction, by default it will be used, so you don't need to begin/commit transaction yourself. (Assume v2026.)
Hmm. I’m using 2024 and I believe (by looking at the code) that it only uses transactions in “grid…”. It doesn’t appear to use transactions otherwise (though I could easily have missed it). Did I or is this new since 2024?
If 2026 does fully support it, I would assume it starts the transaction just before calling Row_Inserting/Updating() and rolls back if the insert fails (without ever calling Row_Inserted/Updated() and commits at the end of Row_Inserted/Updated? That would make the most sense.
I will be upgrading to 2027 as PHP gets just a little too out of date every 3 years but I’ve got a lot of tidyup remaining that I’m doing in 2024.
Important note: Thank you so very much for all of your advice and guidance in this forum. PHPMaker is a good product with a steep learning curve and somewhat challenging documentation into a great experience of achieving exactly what I need. Again, thank you.
I believe v2024 was the same (i.e. use transaction by default if supported by database), so you should be able to use Row_Inserting like:
$conn = $this->getConnection();
$inserted = ...; // Your code (you may want to use try/catch)
if (!$inserted) {
if ($this->UseTransaction) { // Rollback transaction
if ($conn->isTransactionActive()) {
$conn->rollback();
return false;
}
}
}
However, note that:
If you use try/catch and error occurs, the BILLING record is not inserted, there is no need to rollback transaction at all.
There should be no need to commit transaction yourself.
If, indeed, 2024 is like 2026 this would be an excellent solution but I am unable to confirm if they are the same. Also, I wouldn’t have to do the rollback at all as this assumes the framework already started the transaction and therefore returning false would trigger the framework to rollback.
The only transaction use I have found in List.php is in gridInsert and processListAction. The latter I am completely unfamiliar with.
By putting Log() messages in Row…ing/ed and Grid…ing/ed I can see that it is not active for Row Insert or for Row Update but is active for Row Delete (both ing and ed)!!! Also, it is not active for the Gridadd ing or ed but IS active for the rows inside the gridadd, giving a possible workaround for the add.
So unless I restrict to using Gridadd (which I could) and delete/gridadd for updates (very unfriendly), I don’t see how I can accomplish transactional protection - specifically cover a case like row add where I “manually” add a related row to the database in rowinserting or row inserted but the main add fails. If I start transaction and add in rowinserting and the main add fails, there will be no rowinserted to allow me to rollback. If I start the transaction in row inserted and the manual add fails, the main add is not part of the transaction.
Am I doing something wrong? Misunderstanding something? If not, are you sure 2026 actually addressed this by making row adds/updates transactional?
Both versions use transaction, you may verify by checking the generated code. In the Add page, search for "function addRow", then search "beginTransaction()".
list.php. I found addRow() and it clearly contains $conn=$this→getConnection() but, strangely, never uses it in addRow() and there is nothing at all about transactions (checking the useTransaction flag, testing for a transaction in progress, starting, committing or rolling back). Nothing. This supports my Log() test results above.
It seems, therefore, that 2024 does not support transactions properly. Am I missing something? If not, is it all fixed by 2026?
I think for your case, you should simply use Row_Inserted server event that belongs to the master table, in order to check, whether the records in the detail table have been inserted properly.
If there are no records in the detail table inserted, then you should “rollback“ by yourself, by deleting the master record in that event. It’s so simple.
In other words, for such case, you cannot depend on the built-in database transaction. You need to check it by yourself, and handling it based on your own business-logic. Just my two cents.
Transaction is only used in Add page with detail tables (i.e. Master/Detail-Add) where transaction is required (for rollback on insert failure to the detail table).
If you want to use in List page, you do need to begin/commit/rollback transaction yourself. However, as mentioned, you may also use try/catch in Row_Inserting and return false if exception occurs.
Sounds like you two have confirmed that my situation is not covered by the framework. I can get close but there is always an end case I cannot cover. I think the closest I can come is to put the extra insert(s) into Row_Inserted of the main insert and I could add logic to rollback my extras and then explicitly delete the main insert as well if an extra insert fails. A bit harder (but not impossible) with an update. If only there was a Row_UpdateFailed()
Thank you both for helping me come to a reasonable conclusion.
Well, THAT totally makes it possible to do what I want. I start transaction in Row<>ing, commit in Row<>ed and rollback in Row<>FailedEvent. That’s gonna work just fine!
