Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.0 » SQL Remote » Replication Design for SQL Remote » Principles of SQL Remote design » How statements are replicated

 

Replication of triggers

By default, the Message Agent 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 do 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.

An option to replicate trigger actions

The 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 SQL Anywhere fire_triggers option to Off for the Message Agent user ID.