Projects:DB Synchronization With Symmetric DS/How the Trigger Execution Order is Managed

Introduction

SymmetricDS uses triggers to capture the changes done in the synchronized tables. The type of these trigger is AFTER INSERT/UPDATE/DELETE.

It is important that these triggers are executed before Openbravo triggers of the same type, because otherwise there will be referencial integrity problems in the synchronization (see here why, slides 13-15).

PostgreSQL

In PostgreSQL triggers of the same type are executed in alphabetical order by name. SymmetricDS allows to define the name of its triggers, so the aa_ prefix is added to ensure that those triggers are the first executed.

Oracle

Before Oracle 11 the execution order of the triggers was arbitrary, after that it is managed using the FOLLOWS and PRECEDES clauses. Using FOLLOWS in the definition of a trigger allows to specify the list of triggers that should be executed before the one being defined, using PRECEDES allows to specify those that should be executed after.

In theory it is easier to just use the PRECEDES clause when creating the SymmetricDS triggers (at that point the Openbravo triggers are already defined), the problem is that PRECEDES can only be used in reverse crossedition trigger. Not only the trigger being defined must be a reverse crossedition trigger, but also the ones being referenced, and the Openbravo ones are not reverse crossedition triggers.

The only option then is to use FOLLOWS. After installing the SymmetricDS triggers, the Openbravo triggers must be rebuilt after installing the SymmetricDS triggers, to specify that they must follow them.

An ant task has been created to do this automatically: ant install.symmetric.ds. This task will create the SymmetricDS triggers (along with the rest of SymmetricDS database objects), and then will rebuild the affected Openbravo triggers. An analogous task called uninstall.symmetric.ds will uninstall the SymmetricDS triggers and then will recreate the Openbravo triggers to remove the FOLLOWS clause.