Projects:DB Synchronization With Symmetric DS/Trigger Execution Order
Contents |
Introduction
In order to capture changes, SymmetricDS installs triggers in the Openbravo tables that should be synchronized.
The triggers installed by SymmetricDS are highly configurable (see here). They are created so that they are invoked after the row has been inserted/updated/deleted from the database.
The tables where these triggers are created might have their own triggers that are executed after the operation has been done in the database. This document will explain why the execution order of the triggers of the same type is relevant, and how the SymmetricDS module ensures that the trigger execution order is proper in order to avoid synchronization errors.
Why the trigger execution order is relevant
Triggers created by SymmetricDS must be executed before those that were defined previously in the synchronized tables. This need will be demonstrated with an example.
Suppose that the triggers defined in the Openbravo tables are executed before those created by SymmetricDS. Let's use the C_UOM table as an example. It defines a trigger that automatically creates an entry in the C_UOM_TRL table whenever a new row is added to it. Let's suppose that both the C_UOM and C_UOM_TRL tables are configured to be synchronized. This chain of events could potentially happen:
- An entry is added to C_UOM
- Its c_uom_trg trigger is executed, it adds an entry in c_uom_trl
- The SymmetricDS trigger of C_UOM_TRL is invoked, which results in the new change being stored in the SYM_DATA table.
- The SymmetricDS trigger of C_UOM is invoked, which results in the new change being stored in the SYM_DATA table.
If this happens, the SYM_DATA will contain two entries, the first one representing the insertion in C_UOM_TRL and the second one the insertion in C_UOM. Changes are imported in the target nodes in the order they were inserted in the SYM_DATA table, so when the C_UOM_TRL entry is imported there will be a synchronization error because the C_UOM entry it references has not yet been imported.
If the SymmetricDS triggers would have been executed before the other triggers, this situation cannot happen, so the synchronization error would be prevented.
Trigger execution order in PostgreSQL
In PostgreSQL triggers are invoked alphabetically. To ensure the SymmetricDS triggers are invoked first, the triggers name use a prefix 'aaa_'.
Trigger execution order in Oracle
Before Oracle 11g the trigger execution order was not defined. Oracle 11g defines the FOLLOWS clause, which can be used to specify the execution order of the triggers.
Given that the SymmetricDS triggers must be executed first, the FOLLOWS clause must be added to the triggers predefined in Openbravo. The affected Openbravo triggers automatically recreated to add the FOLLOWS clause when SymmetricDS is installed. They are also automatically recreated to remove the FOLLOWS clause when SymmetricDS is uninstalled.