Sybase DC38133-01-0902-01 manual Adding a subscription-list column to the Contact table

No log entries for the When a customer is reassigned, the Contact table is unaffected. There are no

Contact table when changes to the Contact table, and consequently no entries in the transaction

territories realigned 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-listTo solve this problem in Adaptive Server Enterprise, you must add a column

columnto 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.

152