Data Replication with SQL Remote
   PART 2. Replication Design for SQL Remote
     CHAPTER 8. SQL Remote Design for Adaptive Server Enterprise       

Partitioning tables that do not contain the subscription column

In many cases, the rows of a table need to be partitioned even when the subscription column does not exist in the table. This section describes how to handle this case, using an example.

  The Contact example

The Contact database illustrates why and how to partition tables that do not contain the subscription column.

Example 

Here is a simple database that illustrates the problem. We call this database the Contact database, because it contains a Contact table in addition to the two tables described earlier in this chapter.

Each sales representative sells to several customers. At some customers there is a single contact, while other customers have several contacts.

The tables in the database 

The three tables are described in more detail as follows:

Table	Description
SalesRep	All sales representatives that work for the company. The SalesRep table has the following columns: rep_key     An identifier for each sales representative. This is the primary key. name     The name of each sales representative. The SQL statement creating this table is as follows: CREATE TABLE SalesRep ( rep_key CHAR(12) NOT NULL, name CHAR(40) NOT NULL, PRIMARY KEY (rep_key) ) go
Customer	All customers that do business with the company. The Customer table includes the following columns: cust_key     An identifier for each customer. This is the primary key. name     The name of each customer. rep_key     An identifier for the sales representative in a sales relationship. This is a foreign key to the SalesRep table. The SQL statement creating this table is as follows: CREATE TABLE Customer ( cust_key CHAR(12) NOT NULL, name CHAR(40) NOT NULL, rep_key CHAR(12) NOT NULL, FOREIGN KEY ( rep_key ) REFERENCES SalesRep, PRIMARY KEY (cust_key) ) go
Contact	All individual contacts that do business with the company. Each contact belongs to a single customer. The Contact table includes the following columns: contact_key     An identifier for each contact. This is the primary key. name     The name of each contact. cust_key     An identifier for the customer to which the contact belongs. This is a foreign key to the Customer table. The SQL statement creating this table is: CREATE TABLE Contact ( contact_key CHAR(12) NOT NULL, name CHAR(40) NOT NULL, cust_key CHAR(12) NOT NULL, FOREIGN KEY (cust_key) REFERENCES Customer, PRIMARY KEY (contact_key) ) go

Replication goals 

The goals of the design are to provide each sales representative with the following information:

• The complete SalesRep table.

• Those customers assigned to them, from the Customer table.

• Those contacts belonging to the relevant customers, from the Contact table.

• Maintenance of proper information when Sales Representative territories are realigned.

  Territory realignment in the Contact example

In territory realignment, rows are reassigned among subscribers. In the current example, territory realignment involves reassigning customers among the sales representatives. It is carried out by updating the rep_key column of the Customer table.

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.

No log entries for the Contact table when territories realigned 

When a customer is reassigned, the Contact table is unaffected. There are no changes to the Contact table, and consequently no entries in the transaction log pertaining to the Contact table. In the absence of this information, SQL Remote cannot reassign the rows of the Contact table along with the Customer. This failure would cause referential integrity problems: the Contact table at the remote database of the old sales representative contains a cust_key value for which there is no longer a Customer.

In this section, we describe how to reassign the rows of the Contact table.

  Partitioning the Customer table in the Contact example

The Customer table can be partitioned using the rep_key value as a subscription column. A publication that includes the SalesRep and Customer tables would be as follows:

exec sp_add_remote_table 'SalesRep'
exec sp_add_remote_table 'Customer'
go
exec sp_create_publication 'SalesRepData'
go
exec sp_add_article 'SalesRepData', 'SalesRep'
exec sp_add_article SalesRepData, 
         Customer, NULL, 
         'rep_key'
go

  Adding a subscription-list column to the Contact table

The Contact table must also be partitioned among the sales representatives, but contains no reference to the sales representative rep_key value.

Add a subscription-list column 

To solve this problem in Adaptive Server Enterprise, you must add a column to the Contact table containing a comma-separated list of subscription values to the row. ( In the present case, there can only be a single subscription value.) The column can be maintained using triggers, so that applications against the database are unaffected by the presence of the column. We call this column a subscription-list column.

When a row in the Customer table is inserted, updated or deleted, a trigger updates rows in the Contact table. In particular, the trigger updates the subscription-list column. As the Contact table is marked for replication, the before and after image of the row is recorded in the log.

A subscription-list column is a column added to a table for the sole purpose of holding a comma-separated list of subscribers. In the present case, there can only be a single subscriber to each row of the Contact table, and so the subscription-list column holds only a single value.

     For a discussion of the case where the subscription-list column can hold many values, see Sharing rows among several subscriptions.

Contact table definition 

In the case of the Contact table, the table definition would be changed to the following:

CREATE TABLE Contact (
   contact_key CHAR( 12 ) NOT NULL,
   name CHAR( 40 ) NOT NULL,
   cust_key CHAR( 12 ) NOT NULL,
   subscription_list CHAR( 12 ) NULL,
   FOREIGN KEY ( cust_key )
   REFERENCES Customer ( cust_key ),
   PRIMARY KEY ( contact_key )
)
go

The additional column is created allowing NULL, so that existing applications can continue to work against the database without change.

The subscription_list column holds the rep_key value corresponding to the row with primary key value cust_key in the Customer table. A set of triggers handles maintenance of the subscription_list column.

     For an Adaptive Server Anywhere consolidated database, the solution is different. For more information, see Partitioning tables that do not contain the subscription expression.

  Maintaining the subscription-list column

In order to keep the subscription_list column up to date, triggers are needed for the following operations:

• INSERT on the Contact table.

• UPDATE on the Contact table.

• UPDATE on the Customer table.

The UPDATE of the Customer table addresses the territory realignment problem, where customers are assigned to different Sales Reps.

An INSERT trigger for the Contact table 

The trigger for an INSERT on the Contact table sets the subscription_list value to the corresponding rep_key value from the Customer table:

CREATE TRIGGER set_contact_sub_list
ON Contact
FOR INSERT
AS
BEGIN
   UPDATE Contact
   SET Contact.subscription_list = (
         SELECT rep_key
         FROM Customer
         WHERE Contact.cust_key = Customer.cust_key )
   WHERE Contact.contact_key IN (
         SELECT contact_key
         FROM inserted
   )
END

The trigger updates the subscription_list column for those rows being inserted; these rows being identified by the subquery

SELECT contact_key
FROM inserted

An UPDATE trigger for the Contact table 

The trigger for an UPDATE on the Contact table checks to see if the cust_key column is changed, and if it has updates the subscription_list column.

CREATE TRIGGER update_contact_sub_list
ON Contact
FOR UPDATE
AS
IF UPDATE ( cust_key )
   BEGIN
      UPDATE Contact
      SET subscription_list = Customer.rep_key
      FROM Contact, Customer
      WHERE Contact.cust_key=Customer.cust_key
   END

The trigger is written using a join; a subquery could also have been used.

An UPDATE trigger for the Customer table 

The following trigger handles UPDATES of customers, transferring them to a new Sales Rep:

CREATE TRIGGER transfer_contact_with_customer
ON Customer
FOR UPDATE
AS
IF UPDATE ( rep_key )
BEGIN
   UPDATE Contact
   SET Contact.subscription_list = (
      SELECT rep_key
      FROM Customer
      WHERE Contact.cust_key = Customer.cust_key )
   WHERE Contact.contact_key IN (
         SELECT cust_key
      FROM inserted
   )
END