In the following example, three tables store information about the interactions between sales representatives and customers: Customers, Contacts, and SalesReps. Each sales representative sells to several customers. For some customers, there is a single contact, and for other customers there are multiple contacts.

For detailed descriptions of the tables, see Description of Contacts, Customers, and SalesReps tables.

A sales representative subscribes to a publication that provides a copy of the SalesRep table, a copy of the Customers table with the details of the customers assigned to them, and a copy of the Contacts table with the details of the contacts that correspond to their customers. For example, each sales representative subscribes to the following publication:

CREATE PUBLICATION SalesRepData (
   TABLE SalesReps
   TABLE Customers
      SUBSCRIBE BY rep_key
   TABLE Contacts
      SUBSCRIBE BY (SELECT rep_key
         FROM Customers
         WHERE Contacts.cust_key = Customers.cust_key )
);

For a detailed description of this publication, see Using disjoint data partitions.

Maintaining referential integrity

This reassignment of rows among subscribers is sometimes called territory realignment because it is a common feature of sales force automation applications, where customers are periodically reassigned among representatives.

On the consolidated database, when a customer is reassigned to a new sales representative, the rep_key value in the Customers table is updated.

The following statement reassigns a customer, cust1, to another sales representative, rep2.

UPDATE Customers
SET rep_key = 'rep2'
WHERE cust_key = 'cust1';

This update is replicated:

The Contacts table is not changed. There are no entries in the consolidated database transaction log about the Contacts table. As a result, SQL Remote on the remote databases cannot reassign the cust_key rows of the Contacts table. This inability causes the following referential integrity problem: the Contacts table on the remote database of the old sales representative contains a cust_key value for which there is no longer a customer.

A solution is to use a BEFORE UPDATE trigger. A BEFORE UPDATE trigger does not make any change to the database tables, but does create an entry in the consolidated database transaction log.

This BEFORE UPDATE trigger must be fired:

For example, the following statement creates a BEFORE UPDATE trigger.

CREATE TRIGGER "UpdateCustomer" BEFORE UPDATE OF "rep_key"
// only fire the trigger when we modify rep_key, not any other column
ORDER 1 ON "Cons"."Customers"
/* REFERENCING OLD AS old_name NEW AS new_name */
REFERENCING NEW AS NewRow
   OLD AS OldRow
FOR EACH ROW 
BEGIN
// determine the new subscription expression
   // for the Customers table
	 UPDATE Contacts
   PUBLICATION SalesRepData
   OLD SUBSCRIBE BY ( OldRow.rep_key )
   NEW SUBSCRIBE BY ( NewRow.rep_key )
   WHERE cust_key = NewRow.cust_key;
END
;

SQL Remote uses the information placed in the transaction log to determine which subscribers receive which rows.

The consolidated database transaction log contains two entries after this statement is executed:

SQL Remote scans the transaction log for the BEFORE and AFTER tags. Based on this information, it can determine which remote users get an INSERT, UPDATE, or DELETE statement.

When the BEFORE and AFTER lists are identical, the remote user already has the row and an UPDATE statement is sent.

Notes on the trigger

In the following example, you must use a BEFORE UPDATE trigger. In other contexts, BEFORE DELETE and BEFORE INSERT are necessary.

UPDATE table-name
PUBLICATION pub-name
   SUBSCRIBE BY sub-expression
WHERE search-condition;

In this example, you use a BEFORE trigger.

UPDATE table-name
PUBLICATION publication-name
   OLD SUBSCRIBE BY old-subscription-expression
   NEW SUBSCRIBE BY new-subscription-expression
WHERE search-condition;

The UPDATE statement lists the affected publication and table. The WHERE clause in the statement describes the affected rows. This UPDATE statement does not change the data in the table; it makes entries in the transaction log.

In this example, the subscription expression returns a single value. However, subqueries returning multiple values can also be used. The value of the subscription expression must be the value after the update.

In this example, the only subscriber to the row is the new sales representative. For an example of a row that has existing and new subscribers, see Using overlap partitions.