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

SQL Anywhere 11.0.0 » SQL Remote » Replication Design for SQL Remote » SQL Remote installation design » Partitioning tables that do not contain the subscription expression


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 Contacts

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:

   OLD as OldRow
   // determine the new subscription expression
   // for the Customers table
   UPDATE Contacts
   OLD SUBSCRIBE BY ( OldRow.rep_key )
   NEW SUBSCRIBE BY ( NewRow.rep_key )
   WHERE cust_key = NewRow.cust_key;
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
  • Here is what the UPDATE statement clauses mean:
  • The table-name indicates the table that must be modified at the remote databases.
  • The publication-name indicates the publication for which subscriptions must be changed.
  • The value of subscription-expression is used by the Message Agent to determine both new and existing recipients of the rows. Alternatively, you can provide both OLD and NEW subscription expressions.
  • The WHERE clause specifies which rows are to be transferred between subscribed databases.
Notes on the trigger
  • If the trigger uses the following syntax:
    UPDATE table-name
    PUBLICATION pub-name
       SUBSCRIBE BY sub-expression
    WHERE search-condition;

    the trigger must be a BEFORE trigger. In this case, a BEFORE UPDATE trigger. In other contexts, BEFORE DELETE and BEFORE INSERT are necessary.

  • If the trigger uses the alternate syntax:
    UPDATE table-name
    PUBLICATION publication-name
       OLD SUBSCRIBE BY old-subscription-expression
       NEW SUBSCRIBE BY new-subscription-expression }
    WHERE search-condition;

    The trigger can be a BEFORE or AFTER trigger.

  • The UPDATE statement lists the publication and table that is affected. The WHERE clause in the statement describes the rows that are affected. No changes are made to the data in the table itself by this UPDATE, it makes entries in the transaction log.
  • The subscription expression in this example returns a single value. Subqueries returning multiple values can also be used. The value of the subscription expression must the value after the UPDATE.

    In this case, the only subscriber to the row is the new sales representative. In Sharing rows among several subscriptions, there are existing as well as new subscribers.

Information in the transaction log

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

  • Assume the following data:
    • SalesReps table
    rep_key Name





    • Customers table
    cust_key name rep_key





    SQL Anywhere


    • Contacts table
    contact_key name cust_key







  • Now apply the following territory realignment Update statement
    UPDATE Customers
    SET rep_key = 'rep2'
    WHERE cust_key = 'cust1';

    The transaction log would contain two entries arising from this statement: one for the BEFORE trigger on the Contacts table, and one for the actual UPDATE to the Customers table.

    SalesRepData - Publication Name
    rep1 - BEFORE list
    rep2 - AFTER list
    UPDATE Contacts
    SET contact_key = 'contact1',
        name = 'David',
        cust_key = 'cust1'
    WHERE contact_key = 'contact1'
    SalesRepData - Publication Name
    rep1 - BEFORE list
    rep2 - AFTER list
    UPDATE Customers
    SET rep_key = 'rep2'
    WHERE cust_key = 'cust1'

    The Message Agent scans the log for these tags. Based on this information it can determine which remote users get an INSERT, UPDATE or DELETE.

    In this case, the BEFORE list was rep1 and the AFTER list is rep2. If the before and after list values are different, the rows affected by the UPDATE statement have "moved" from one subscriber value to another. This means the Message Agent will send a DELETE to all remote users who subscribed by the value rep1 for the Customers record cust1 and send an INSERT to all remote users who subscribed by the value rep2.

    If the BEFORE and AFTER lists are identical, the remote user already has the row and an UPDATE will be sent.