ZF2 saving to multiple tables, (1) roll back changes if database error (2) display db errors

Question

In ZF2, I have a mapper that saves data into 3 tables. Table 1 has only ids, tables 2 and 3 have the ids and more data. The tables are filled in turn: first table 1, then table 2, then table 3.

Suppose there is something that prevents saving to the 3rd table (failure of unique constraint, for example). Because the tables are filled in turn, the tables 1 and 2 are filled, but the 3rd isn't. So I'm left with the unused data in tables 1 and 2.

How to get rid of the values created in the tables 1 and 2 if there was a database error, and display the error message in the 3rd table?

You should probably wrap the writes into a single transaction, so if anything fails, it all rolls back. — Matt Gibson, Mar 07 '16 at 17:59

score 0 · Accepted Answer · edited May 23 '17 at 12:31

Found the SO answer.

https://stackoverflow.com/a/13862746/4685379

This answer, though, doesn't specify how to implement the inserts to multiple tables. The key is to put all the inserts between beginTransaction() and commit() methods like this:

try {
    $this->getAdapter()->getDriver()->getConnection()->beginTransaction();

    // your inserts go here

    $this->getAdapter()->getDriver()->getConnection()->commit();
} catch (\Exception $e) {
    $this->getAdapter()->getDriver()->getConnection()->rollback();
    throw $e;
}

The rollback() then goes into the catch block.

ZF2 saving to multiple tables, (1) roll back changes if database error (2) display db errors

1 Answers1