In some situations, UPDATE triggers require special design and coding.
You might expect that if you UPDATE a record on the master, and if that UPDATE operation initiates an UPDATE trigger, then when the record is sent to the replica (by using a REFRESH), an UPDATE trigger will also be initiated on the master. In fact, however, this is not the case. If a record is updated on the master, and the same record is "updated" by using a REFRESH operation on the replica, then the replica server will actually initiate a DELETE+INSERT pair of triggers, not an UPDATE trigger. The reason for this is that the replica does not directly update the record; instead it deletes the old record and then inserts the new record.
Description of the possible causes of triggers
The server can also do an additional pair of DELETE+INSERT operations that is related to synchronization and that causes triggers to be initiated. Prior to copying the master data to the replica tables, the replica database "undoes" all local data changes that have been written into the replica database since the last synchronization. The reason for this is that the replica data is always "tentative" until it has been processed and made official by the master database. If the replica database has tentative data, then each time that it receives official data from the master database, the replica database simply discards any unofficial data that it has and stores only the official data from the master database. The process of "undoing" the tentative changes on the data causes the replica database to do an additional DELETE and INSERT.
If the REFRESH is incremental, then the process is executed in multiple steps. The first step is effectively to throw out all tentative changes made since the last official data was received, then restore the last official data. The second step is to process the new official data. As a result, the replica has all the old official data as of the last refresh, and all the official changes since that refresh; the replica database has exactly what the master has.
For example:
▪ At 10:00, the replica database gets a full refresh with data records A, B, and C.
▪ At 10:01, record A is updated.
▪ At 10:02, record B is updated.
▪ At 10:03, the changes are propagated to the master database, in this case the changes to records A and B. For the purpose of this example, assume that the changes to record A are accepted by the master database and the changes to record B are rejected.
▪ At 10:04, the replica database receives an incremental refresh. During the processing of the incremental refresh, the replica database discards all changes since the last official refresh (in this case the one at 10:00). The changes to record A and B are discarded from the replica database.
At this point, the replica database has the same data as after the last refresh operation and thus is ready to get an incremental subscription that contains only those changes approved by the master since the last refresh.
During the incremental refresh, the replica receives an updated record A (with the propagated data that was accepted by the master database). The changes to record B, which were rejected by the master, are gone forever. The net result is that the database has the official values for all three records - the new, approved changes for record A, and the older (most recent official) data for records B and C.
(Note that the preceding description uses some simplifying assumptions, including that records B and C were not changed on the master since the last official refresh by this replica.)
The following example describes a complete cycle, starting with an update on the replica database and ending with the refresh from the master database, an identifies all the triggers that might be executed during this cycle. In the example, a replica database makes a local update that is propagated to the master database and executed there, and then the master version of the update is downloaded back to replica database in the result set of the synchronization. During this cycle, triggers are initiated in master and replica databases.
1 The update on the replica database initiates regular UPDATE triggers (both the BEFORE and AFTER triggers) in the replica database. If the replica database contains any old master values (which it probably does unless it is brand new and has never done a REFRESH), those data values from the master database are written to the history table in the replica database.
2 The replica database then propagates changes to the master database. When executing the propagated transaction in the master database, UPDATE triggers are initiated in the master database. The old value of the row to be updated is written to the sync history table of the master database. The new value of the row is written to the main table.
3 After the replica database requests a refresh, the master database assembles the result set of the subscription. The result set contains the primary key values of the old versions of updated rows. These rows will be deleted from the replica table. After those rows are deleted, the new values for the same rows are inserted. (The result set that is sent to the replica database will contain a list of the primary keys to be deleted, followed by the rows to be inserted.)
4 When the replica database receives the sync message from the master database, all "tentative" data is deleted from the main tables. This initiates a delete trigger (Delete local tentative row). After this, the replica database inserts the old master version of the row (if it exists) from the sync history table. This initiates an insert trigger (Insert old official row). After this, the server applies the deletes from the synchronization message. This initiates a delete trigger (Delete old official row). Finally, the server applies the inserts from the synchronization message. This initiates an insert trigger (Insert new official row).
For an example that shows the overall process of propagating and refreshing a record, see Propagate and refresh example. The following table shows the details of just the incremental refresh portion of the process.
Action
Illustration
Initial state (after the propagation but prior to the incremental refresh).
Records A and B were changed since the last refresh, but the changes to record B were rejected by the master database.
Changes in the replica database since the last refresh are rolled back in two steps:
▪ The records that changed since the last refresh are deleted.
▪ The most recent "official" records are inserted by copying from the sync history table in the replica database.
The replica database now has the same data as after the last refresh.
New record values (for record A) that are received from the master database are applied in two steps:
▪ Record A is deleted from the replica database.
▪ Record A (with the most recent "official" values received from the master) is inserted into the replica database.
When an update operation that might have taken place in both replica and master databases is synchronized, up to four sync-related triggers might execute on the replica database:
▪ Delete current tentative record
▪ Insert old official record
▪ Delete old official record
▪ Insert new official record
In addition, some triggers on the master database might also execute.
The possible values of the SYS_SYNC_OPERATION_TYPE parameter in DELETE triggers are:
▪ CURRENT_TENTATIVE_DELETE (set when deleting the current locally updated value of a row prior to executing the reply message in replica)
▪ OLD_OFFICIAL_DELETE (set when deleting a row that was deleted in master)
▪ OLD_OFFICIAL_UNIQUE_DELETE (set when the master sends a row to be added to the replica, but a similar row already exists on the replica. With this parameter value, the old official row is deleted before the new row is added to the replica.)
▪ OLD_OFFICIAL_UPDATE (set when executing a delete that was created as a result of an update in master)
The possible values of the SYS_SYNC_OPERATION_TYPE parameter in INSERT triggers are:
▪ OLD_OFFICIAL_INSERT (set when restoring the old master value prior to executing the reply message in replica)
▪ NEW_OFFICIAL_INSERT (set when inserting row that was inserted in master)
▪ NEW_OFFICIAL_UPDATE (set when executing an insert that was created as a result of an update in master)
If the trigger is initiated by a local transaction (not by synchronization logic), then the value of this parameter is NULL.
The above is valid only for incremental refreshes. If the result set is a full refresh, all local data is deleted and a full set of master data is applied. In this case the DELETE operations from the master database are not sent to the replica database. To find out whether the refresh was full or incremental read the value of the bulletin board parameter SYS_SYNC_RESULTSET_TYPE. The possible values are:
▪ FULL
▪ INCREMENTAL
Executing the code intended for the UPDATE triggers
It is not trivial to ensure that an UPDATE operation on the master database is executed as an UPDATE on the replica database. Instead of directly forcing an UDPATE trigger on the replica database to be executed, the master database posts two parameters on the Advanced Replication parameter bulletin board. These parameters can be read by the trigger logic on the replica database to determine how the record was originally processed on the master database. The replica database can then execute the logic appropriate for an UDPATE operation, if desired.
The bulletin board parameter(s) tell you how the record was originally processed — for example, whether it was originally part of an UDPATE operation on the master database. Since an INSERT or DELETE trigger has no easy way to stop itself and then force the execution of an UPDATE trigger, merely knowing that the original statement was an UPDATE trigger is not sufficient. You must modify the code inside your triggers so that they take different actions depending on what the original statement was (for example, whether the original statement on the master database was an UPDATE operation or was a DELETE/INSERT pair). If necessary, your DELETE or INSERT trigger will call the same code that your UPDATE trigger would have called. You should complete the following actions on the replica databases:
For each of the six possible triggers on a table
BEFORE UPDATE, AFTER UDPATE, BEFORE INSERT, AFTER INSERT, BEFORE DELETE, AFTER DELETE,
extract all of the trigger logic into a stored procedure that can be called from inside a trigger.
Thus, for example, if your BEFORE UPDATE trigger looked like:
CREATE TRIGGER trigger_name ON table1 BEFORE UPDATE BEGIN stmt1; stmt2; stmt3; END;
You will now have something like:
CREATE PROCEDURE before_update_on_table1 BEGIN stmt1; stmt2; stmt3; END;
CREATE TRIGGER trigger_name ON table1 BEFORE UDPATE... BEGIN CALL before_update_on_table1; END;
When you have done this, you can rewrite your triggers so that they take into account whether the record was originally an UPDATED operation or an INSERT operation.For example, the logic of your BEFORE INSERT trigger will look something like:
CREATE TRIGGER trig1 ON table1 BEFORE INSERT... BEGIN; IF (get_param('SYS_SYNC_OPERATION_TYPE') = 'NEW_OFFICIAL_UPDATE') THEN IF (get_param('SYS_SYNC_RESULTSET_TYPE') = 'INCREMENTAL') THEN CALL before_update; ELSE ... END IF; ELSE IF (get_param('SYS_SYNC_OPERATION_TYPE') = 'NEW_OFFICIAL_INSERT') THEN CALL before_insert; - do what is normally done in the INSERT trigger. ELSE ... END IF; END;
Moving the body of your triggers to a stored procedures is relatively easy, since triggers and stored procedure language are essentially the same.
Note The "Before" values from the master database are not available, even if you use the "BEFORE" keyword in the triggers on the replica database.
