Transaction Support

ERW provides partial support for SQL transactions and constraints. The support does not work presently for MySQL.

The usefulness of SQL-based constraints is evident if you access your database with different clients: in this case, including constraints at the SQL level avoids duplicating efforts.

ERW runs all update and delete operations inside a transaction. In case of an SQL error, the transaction is rolled back. The same happens when a post-update hook or a post-delete hook returns a non-null value.

However, to avoid cluttering the code logic, SQL errors cause a log to be written, and no further action being taken. This usually results in the user seeing an empty form, or a form with empty lists.

The notable exception, however, is the insertion or update of entities, and the commit phase at the end of an update or delete operation. In this case, errors lead to presenting again to the user the form that was submitted, displaying the error message coming from the database (unfortunately, the message coming from the database is often not exactly explicative).

Thus, you can easily add SQL constraints to your database, if they are checked during one of the phases above. For instance, deferred constraints are checked during the commit phase; thus, they are handled correctly by ERW.

The endorsed way of writing SQL-based constraints is thus to write SQL views and constraints that incorporates your checks, and call the functions from a post-update or post-delete hook.