By Philippe Bonneau on
5/21/2014 11:06 PM
For remember, on SQL databases, triggers permit to change the behaviour of insertions, modifications or deletions made by a program, by adding SQL notification callbacks. This permits to make complex check on field values, or to copy some record in audit tables for example.
On Oracle, a trigger can be implemented after or before the action. On before triggers, values can be changed by the notification callback. On after, we are sure that any modification done by the program or on before triggers is taken into account in the :new record.
Sometimes, complex check should be made on several tables, during a transactional modification for example. In such a case, a BEFORE COMMIT trigger is mainly required. Even if it is more recommanded to do such a check in the client program, on third party databases, there is no choice, additional checks can be done only on the database server. Unfortunately, Oracle does not permit such a trigger implementation directly, a workaround has to be developped. This is the purpose of this post.