Neither the SQL standard nor any of the major database vendors provide an ON COMMIT trigger (i.e. a trigger that fires when a transaction commits). The only exception (AFAIK) is Firebird

I wonder why that is. I think this kind of trigger would be very useful, specially to check that the modifications performed during the transaction do not violate any of the integrity constraints of the schema. And I'm not talking about complex business rules, even to enforce some association multiplicity constraints we should be able to check the database state at the end of the transaction, do it in-between is not good enough. Moreover, an on commit trigger could avoid the the too-common "mutating tables" error typically arising when using triggers that query the same tables we are modifying (as usually happens when we want to check that the modifications are consistent with the constraints).

Let me just give you an example, assume that we need to generate the database schema for this UML class diagram

where we are assuming that all students have to be enrolled in at least one course. How do you enforce this constraint at the database level? What kind of trigger do you use? An AFTER DELETE trigger on the EnrolledIn table is only a partial solution since it checks that existing students do not drop out of his/her courses but it does not check that new students enroll at least in one course (an AFTER INSERT trigger on Student cannot be used for this purpose since the trigger will execute immediately after inserting a new student and thus it cannot check whether before committing the transaction we have enrolled the student in some courses).

Of course, there are workarounds, e.g. enforcing the constraint at the application level instead of relying on the database. However, I prefer to put as many constraints as possible in the database (IMHO it's safer, when checking constraints in the application logic we need to remember to include the evaluation of all relevant constraints every time we create a new module)

The absence of this kind of trigger is even more surprising when considering that database vendors (e.g. Oracle) do provide the option of deferring the evaluation of primary, foreign, unique and check constraints until the end of the transaction. An On-Commit trigger would be exactly the same concept but with more expressiveness power.

Anyway, if you were wondering why my UMLtoDB generation service ignores multiplicity constraints now you know.

If you enjoyed this post you can subscribe to this Software Modeling blog and/or follow me on twitter or through the portal's mailing list . And if you really liked it help me pass it on to others by bookmarking and sharing the post using the links below: