Maintaining referential integrity when reassigning rows among subscribers

To cancel a sales relationship between a customer and a sales representative, a row in the Policy table is deleted. In this example, the Policy table change is properly replicated to the old sales representative. However, no change has been made to the Customers table, and so no changes to the Customers table are replicated to the old sales representative.

In the absence of triggers, this can leave a subscriber with incorrect data in their Customers table. The same kind of problem arises when a new row is added to the Policy table.

Using triggers to solve the problem

The solution is to write BEFORE triggers that fire when changes are made the Policy table These special triggers makes no changes to the database tables, but they do make an entry in the transaction log that SQL Remote uses to maintain data in subscriber databases.

A BEFORE INSERT trigger

For example, the following statements create a BEFORE INSERT trigger that tracks inserts into the Policy table, and ensures that remote databases contain the proper data.

CREATE TRIGGER InsPolicy
BEFORE INSERT ON Policy
REFERENCING NEW AS NewRow
FOR EACH ROW
BEGIN
   UPDATE Customers
   PUBLICATION SalesRepData
   SUBSCRIBE BY (
      SELECT rep_key
      FROM Policy
      WHERE cust_key = NewRow.cust_key
      UNION ALL
      SELECT NewRow.rep_key
   )
   WHERE cust_key = NewRow.cust_key;
END;

A BEFORE DELETE trigger

The following statements create a BEFORE DELETE trigger that tracks deletes from the Policy table:

CREATE TRIGGER DelPolicy
BEFORE DELETE ON Policy
REFERENCING OLD AS OldRow
FOR EACH ROW
BEGIN
   UPDATE Customers
   PUBLICATION SalesRepData
   SUBSCRIBE BY (
      SELECT rep_key
      FROM Policy
      WHERE cust_key = OldRow.cust_key
      AND Policy_key <> OldRow.Policy_key
   )
   WHERE cust_key = OldRow.cust_key;
END;

The SUBSCRIBE BY clause of the UPDATE PUBLICATION statement contains a subquery, and this subquery can be multiple-valued.

Multiple-valued subqueries

The subquery in the SUBSCRIBE clause of the UPDATE PUBLICATION is a UNION expression, and can be multiple-valued:

...
SELECT rep_key
FROM Policy
WHERE cust_key = NewRow.cust_key
UNION ALL
SELECT NewRow.rep_key
...

The first part of the UNION is the set of existing sales representatives dealing with the customer, taken from the Policy table.

The result set of the subscription query must be all those sales representatives receiving the row, not just the new sales representatives.
The second part of the UNION is the rep_key value for the new sales representative dealing with the customer, taken from the INSERT statement.

The subquery in the BEFORE DELETE trigger is multiple-valued:

...
SELECT rep_key
FROM Policy
WHERE cust_key = OldRow.cust_key
AND rep_key <> OldRow.rep_key
...

The subquery takes rep_key values from the Policy table. The values include the primary key values of all those sales representatives who deal with the customer being transferred (WHERE cust_key = OldRow.cust_key), with the exception of the one being deleted (AND rep_key <> OldRow.rep_key).

The result set of the subscription query must be all those values matched by sales representatives receiving the row following the delete.

Notes

Data in the Customers table is not identified with an individual subscriber (by a primary key value, for example) and is shared among more than one subscriber. This allows the possibility of the data being updated at more than one remote site between replication messages, which can lead to replication conflicts. You can address this issue either by permissions (allowing only certain users the right to update the Customers table, for example) or by adding RESOLVE UPDATE triggers to the database to handle the conflicts programmatically.
Updates on the Policy table have not been described here. Either they should be prevented, or a BEFORE UPDATE trigger must be created that combines features of the BEFORE INSERT and BEFORE DELETE triggers shown in the example.

Discuss this page in DocCommentXchange.
Send feedback about this page using email.