SQL Remote User's Guide
Principles of SQL Remote Design
How statements are replicated
Trigger replication in SQL Remote is different for the Adaptive Server Enterprise Message Agent and the Adaptive Server Anywhere Message Agent.
From Adaptive Server Enterprise, trigger actions are replicated. You must ensure that triggers are not fired in the remote Adaptive Server Anywhere databases. If the trigger were fired, its actions would be executed twice.
The Adaptive Server Anywhere FIRE_TRIGGERS database option prevents triggers from being fired. If you set this option for the user ID used by the Message Agent, be careful to not use this user ID for other purposes.
An alternative approach to preventing trigger execution, available only for Adaptive Server Anywhere, is to use the following condition around the body of your triggers:
IF CURRENT REMOTE USER IS NULL
This make execution conditional on whether the current user is the Message Agent.
By default, the Message Agent for Adaptive Server Anywhere does not replicate actions performed by triggers; it is assumed that the trigger is defined remotely. This avoids permissions issues and the possibility of each action occurring twice. There are some exceptions to this rule:
Conflict resolution trigger actions The actions carried out by conflict resolution, or RESOLVE UPDATE, triggers are replicated from a consolidated database to all remote databases, including the one that sent the message causing the conflict.
Replication of BEFORE triggers Some BEFORE triggers can produce undesirable results when using SQL Remote, and so BEFORE trigger actions that modify the row being updated are replicated, before UPDATE actions.
You must be aware of this behavior when designing your installation. For example, a BEFORE UPDATE that bumps 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 will fire when the UPDATE is replicated. To prevent this problem, you must ensure that, at the subscriber database, the trigger is not present or does not carry out the replicated action. Also, a BEFORE UPDATE that sets a column to the time of the last update will get the time the UPDATE is replicated as well.
The Adaptive Server Anywhere Message Agent has an option that causes it to replicate all trigger actions when sending messages. This is the dbremote -t option.
If you use this option, you must ensure that the trigger actions are not carried out twice at remote databases, once by the trigger being fired at the remote site, and once by the explicit application of the replicated actions from the consolidated database.
To ensure that trigger actions are not carried out twice, you can wrap an IF CURRENT REMOTE USER IS NULL ... END IF statement around the body of the triggers or you can set the Adaptive Server Anywhere Fire_triggers option to OFF for the Message Agent user ID.