Handling UPDATE triggers

You might expect that if you UPDATE a record on the master, and if that UPDATE operation fires an UPDATE trigger, then when the record is sent to the replica (via a REFRESH), an UPDATE trigger will also be fired 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” via a REFRESH operation on the replica, then the replica server will actually fire 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.

The server can also do an additional pair of insert-delete operations that is related to synchronization and that causes triggers to be fired. Prior to copying the master data to the replica’s tables, the replica “undoes” all local data changes that have been written into the replica since the last synchronization. The reason for this is that the replica’s data is always “tentative” until it has been processed and made official by the master. If the replica has tentative data, then each time that it receives official data from the master, it (the replica) simply discards any unofficial data that it has and stores only the official data from the master. The process of “undoing” the tentative changes on the data causes the replica server to do an additional delete and insert. We explain this in more detail below.

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. Thus we have all the old official data as of the last refresh, and we have all the official changes since that refresh, so we have exactly what the master has.

Let’s step through an example, in which we get a full refresh from the master, make some changes on the replica, and then get an incremental refresh from the master. During the process of getting the refresh(es) from the master, we throw out all the data on the replica, and store all the data sent by the master.

▪At 10:03, we propagate the changes, in this case the change to Anne’s and Barry’s records. For the purpose of this example, assume that Anne’s changes are accepted by the master and Barry’s changes are rejected.

▪At 10:04, we get an incremental refresh. During the processing of the incremental refresh, the replica discards all changes since the last official refresh (in this case the one at 10:00 AM). Both Anne’s changes Barry’s changes are discarded from the replica.

At this point, our replica looks just as it did 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 our incremental refresh, we get an updated record for Anne (whose propagated data was accepted by the master and then returned during the REFRESH). Barry’s changes, 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 Anne, and the older (most recent official) data for Barry and Carrie.

(Note that the preceding description uses some simplifying assumptions, including that Barry and Carrie’s records were not changed on the master since the last official refresh by this replica.)

Now let’s give an example of a complete cycle, starting with an update on the replica and ending with the refresh from the master, so that we can see all the triggers that may be executed during this cycle. In our example, a replica makes a local update that is propagated to the master and executed there, and then the master version of the update is downloaded back to replica in the result set of the synchronization. During this cycle, triggers are fired in master and replica databases as shown below:

1 The update on the replica fires regular UPDATE triggers (both the BEFORE and AFTER triggers) in the replica. If the replica 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 are written to the history table in the replica.

2 The replica then propagates its data to the master. When executing the propagated transaction in the master, UPDATE triggers are fired in the master. 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 requests a refresh, the master server assembles the result set of the subscription. It 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 of the same rows will be inserted. (The result set that is sent to the replica will contain first a list of primary keys to be deleted, followed by the rows to be inserted.)

4 When the replica receives the sync message from the master, it deletes all “tentative” data from the main tables. This fires a delete trigger (Delete local tentative row). After this, the replica server inserts the possibly existing old master version of the row from the sync history table. This fires an insert trigger (Insert old official row). After this, the server applies the deletes from the synchronization message. This fires a delete trigger (Delete old official row). Finally, the server applies the inserts from the synchronization message. This fires an insert trigger (Insert new official row).

The illustration below shows a detailed breakdown of what occurs during an incremental refresh. Propagate and Refresh showed the overall process of propagating and refreshing a record. The illustration below shows the details of just the incremental refresh portion of the earlier diagram.) As you can see, for the record updated by “Anne”, the replica server actually executes multiple delete and insert operations. The record updated by “Barry” is also deleted and then inserted (rather than simply updated).

2 During the refresh state, there are two steps: 1) the roll back replica changes since the last refresh and 2) new or updated records from the master are inserted. Each of these two steps is itself composed of two sub-steps (delete and insert).

When synchronizing an update operation that may have taken place in both replica and master databases, we have up to four sync-related triggers that may fire on the replica:

(In addition, some trigger(s) on the master may also have fired.)

Now that you understand all the possible triggers that can occur in a advanced replication operation when an UPDATE occurs on the master, you will understand all the possible values that the new bulletin board parameters can take. The possible values are documented in Bulletin board parameters, and are summarized below:

The possible values of the SYS_SYNC_OPERATION_TYPE parameter in DELETE triggers are:

▪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.)

The possible values of the SYS_SYNC_OPERATION_TYPE parameter in INSERT triggers are:

If the trigger is fired by a local transaction (i.e. 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 full, 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. To find out whether the refresh was full or incremental read the value of the bulletin board parameter SYS_SYNC_RESULTSET_TYPE. Its possible values are:

It is not trivial to ensure that an UPDATE operation on the master is executed as an UPDATE on the replica. Instead of directly trying to force an UDPATE trigger on the replica to be executed, the solidDB^® server (the master) posts two parameters on the advanced replication parameter bulletin board. These parameters can be read by the trigger logic on the replica to determine how the record was originally processed on the master. The replica 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. Since an INSERT or DELETE trigger has no easy way to stop itself and then force the firing of an UPDATE trigger, merely knowing that the original command was an UPDATE trigger is not sufficient. You will need to 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 was an update 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 do the following on the replica(s):

For each of the six possible triggers on a table

extract all of the trigger’s logic into a stored procedure that can be called from inside a trigger.

Thus, for example, if your BEFORE UPDATE trigger looked like:

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;

Once you have done this, you can rewrite your triggers so that they take into account whether the record was originally UPDATED or was originally INSERTED. 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;