Sybase SQL Remote DC38133-01-0902-01 User Manual
No log entries for the
Contact table when
territories realigned
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.
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:
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.
but contains no reference to the sales representative rep_key value.
Add a subscription-list
column
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.
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.
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