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

SQL Anywhere 10.0.1 » SQL Remote » SQL Remote Installation Design » Partitioning tables that do not contain the subscription expression

Partitioning the Contacts table in the Contacts example Next Page

Territory realignment in the Contacts example


In territory realignment, rows are reassigned among subscribers. In the present case, territory realignment is the reassignment of rows in the Customers table, and by implication also the Contacts table, among the Sales Reps.

When a customer is reassigned to a new sales rep, the Customers table is updated. The UPDATE is replicated as an INSERT or a or a DELETE to the old and new sales representatives, respectively, so that the customer row is properly transferred to the new sales representative.

For information on the way in which SQL Anywhere and SQL Remote work together to handle this situation, see Who gets what?.

When a customer is reassigned, the Contacts table is unaffected. There are no changes to the Contacts table, and consequently no entries in the transaction log pertaining to the Contacts table. In the absence of this information, SQL Remote cannot reassign the rows of the Contacts table along with the Customers.

This failure will cause referential integrity problems: the Contacts table at the remote database of the old sales representative contains a cust_key value for which there is no longer a Customers.

Use triggers to maintain Contactss

The solution is to use a trigger containing a special form of UPDATE statement, which does not make any change to the database tables, but which does make an entry in the transaction log. This log entry contains the before and after values of the subscription expression, and so is of the proper form for the Message Agent to replicate the rows properly.

The trigger must be fired BEFORE operations on the row. In this way, the BEFORE value can be evaluated and placed in the log. Also, the trigger must be fired FOR EACH ROW rather than for each statement, and the information provided by the trigger must be the new subscription expression. The Message Agent can use this information to determine which subscribers receive which rows.

Trigger definition

The trigger definition is as follows:

CREATE TRIGGER UpdateCustomer
BEFORE UPDATE ON Customers
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;
A special UPDATE statement for publications

The UPDATE statement in this trigger is of the following special form:

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

Notes on the trigger
Information in the transaction log

Understanding the information in the transaction log helps in designing efficient publications.