The database fires triggers as changes are made to tables, and the changes are recorded to the capture log (sym_data) in the same order. However, changes can be captured in the wrong order when custom triggers are placed on the tables that also cause data modifications to be captured.

For example, consider a table with a SymmetricDS trigger on it that also has a custom trigger that performs an update to set the last modified date. The user updates a row with total amount of 0 and last modified date of yesterday to a total amount of 100. First, the custom trigger fires, which updates the last modified date to today, causing the SymmetricDS trigger to fire and capture the row as total amount of 0 and last modified date of today. Second, the SymmetricDS trigger fires and captures the row as total amount of 100 and last modified date of yesterday. The rows are captured out of order, which means they are loaded in the wrong order on the target database, which can cause data to be lost or go into conflict.

How to Preserve Order of Changes

When using custom triggers that cause additional changes to be captured, the SymmetricDS triggers need to be the first to fire for a table. Most database platforms allow multiple triggers on a table, and some allow the order of firing to be controlled, either from an alter statement or calling a stored procedure. Some databases do not allow control of order, so the triggers must be consolidated into a single trigger. Another option for some platforms, when modifying the current table, is to use "before" triggers to manipulate the data.

Use "Before" Triggers for Custom Triggers

Some platforms support "before" and "after" triggers. SymmetricDS triggers are always created as an "after" trigger because the row change was allowed and now must be captured. If a custom trigger is modifying the current row on the current table, then a "before" trigger may offer implicit variables to modify the column values. The variables are commonly prefixed with "old" and "new", and the procedure language allows the custom trigger to manipulate the values directly without performing additional SQL statements. In this case, the "before" trigger running first for a custom trigger is safe, because it modifies the data without running SQL and causing additional triggers to fire.

Consolidate into a Single Trigger

If the order of triggers firing cannot be controlled, then any custom trigger code must be included into the SymmetricDS trigger so there is only one trigger to fire. The custom trigger text can be add to the end the SymmetricDS trigger using the custom_on_insert_text, custom_on_update_text, and custom_on_delete_text fields of sym_trigger.

Set the Order of Triggers

The current triggers and their name can be queried from the sym_trigger_hist table. SymmetricDS uses triggers on its own configuration tables, which can be excluded by looking for the prefix of "SYM".

select source_table_name, name_for_insert_trigger, name_for_update_trigger, name_for_delete_trigger
from sym_trigger_hist
where inactive_time is null
and source_table_name not like 'sym_%'

The SymmetricDS trigger names use a naming convention of "SYM_ON_X_FOR_Y" where X is the I, U, or D for insert, update, or delete triggers, and Y is an abbreviated table name. See the list of database platforms below for the method of setting the order on a trigger.

SQL-Server

The sp_settriggerorder stored procedure is called to set the SymmetricDS trigger to be the first to fire. For example, if a MYTABLE application table is in the MYDB database and DBO schema, the triggers can be set to first with the following:

exec MYDB.sys.sp_settriggerorder @triggername = 'dbo.SYM_ON_I_FOR_MYTBL', @order = 'First', @stmttype = 'INSERT', @namespace = NULL
exec MYDB.sys.sp_settriggerorder @triggername = 'dbo.SYM_ON_U_FOR_MYTBL', @order = 'First', @stmttype = 'UPDATE', @namespace = NULL
exec MYDB.sys.sp_settriggerorder @triggername = 'dbo.SYM_ON_D_FOR_MYTBL', @order = 'First', @stmttype = 'DELETE', @namespace = NULL

PostgreSQL

PostgreSQL executes the triggers in alphabetical order by name. To control the name of SymmetricDS triggers, use the name_for_insert_trigger, name_for_update_trigger, and name_for_delete_trigger fields on the sym_trigger table.

Oracle

Oracle executes triggers in an order that it determines, but the order cannot be controlled by the user. Use trigger consolidation method instead.

MySQL

Older version of MySQL do not allow multiple triggers of the same type on a table. Starting in version 5.7.2, multiple triggers can be created and they execute in the order of creation. The "follows" or "precedes" clause can be used when creating custom triggers to order them after the SymmetricDS triggers. Dropping and creating custom triggers would also cause them to run last.

DB2

DB2 executes triggers in the order in which they were created, so dropping and recreating custom triggers would cause them to run last.

Firebird

Firebird executes triggers in the order specified by their "position" clause, which defaults to 0. The SymmetricDS triggers default to position 0 to be first. Custom triggers can be created with the "position" clause to fire last.