Replicating triggers

Typically, remote databases have the same triggers defined as the consolidated database does.

By default, SQL Remote does not replicate the actions performed by the triggers. Instead, when an action that fires a trigger on the consolidated database is replicated on the remote database, the duplicate trigger is automatically fired on the remote database. This avoids permissions issues and the possibility of each action occurring twice. There are some exceptions to this rule:

Replication of RESOLVE UPDATE triggers The actions carried out by conflict resolution, or RESOLVE UPDATE triggers are replicated from the consolidated database to all remote databases, including the remote database that sent the message that created the conflict. See Default resolution for update conflicts.
Replication of BEFORE triggers The actions of a BEFORE trigger that modifies a row being updated are replicated before the UPDATE statement actions.
For example, a BEFORE UPDATE trigger that increases a counter column in the row to keep track of the number of times a row is updated would double count if replicated as the BEFORE UPDATE trigger fires on the remote database when the UPDATE statement is replicated.

A BEFORE UPDATE trigger that sets a column to the time of the last update also receives the time the UPDATE statement is replicated.

To prevent this problem, you must ensure that, at the subscriber database, the BEFORE UPDATE trigger is not present or does not perform the replicated action.

An option to replicate trigger actions

To replicate all trigger actions when sending messages, use the -t option for the SQL Remote Message Agent (dbremote). See SQL Remote Message Agent utility (dbremote).

When you use the -t option, ensure that the trigger actions are not carried out twice at remote databases, once when the replicated trigger actions are applied, and once when the trigger is fired on the remote database.

To ensure that trigger actions are not carried out twice, use one of the following options:

Wrap an IF CURRENT REMOTE USER IS NULL ... END IF statement around the body of the trigger.
Set the SQL Anywhere fire_triggers option to Off for the SQL Remote user name. See fire_triggers option.

Avoiding trigger errors

If a publication includes only a subset of a database, a trigger at the consolidated database can refer to tables or rows that are present at the consolidated database, but are not present on the remote databases. When such a trigger is fired on the remote database, errors occur. To avoid these errors, use an IF statement to make the trigger actions conditional, and:

Have the actions of the trigger be conditional on the value of CURRENT PUBLISHER. See CURRENT PUBLISHER special value.
Have the actions of the trigger be conditional on the object_id function not returning NULL. The object_id function takes a table or other object as argument, and returns the ID number of that object or NULL if the object does not exist. See System functions.
Have actions of the trigger be conditional on a SELECT statement that determines if the rows exist.