Nolog entr ies forthe
Contacttable when
territories realigned
Whena customer is reassigned, the Contact table is unaffected. Thereare no
changesto the Contact table, and consequently no entries in the transaction
logpertaining to the Contact table. In the absence of this information,
SQLRemote cannot reassign the rows of the Contact table along with the
Customer. This failure would cause referential integrity problems: the
Contacttable at the remote database of the old sales representative contains
acust_keyvalue for which there is no longer a Customer.
Inthis section, we describe how to reassign the rows of the Contact table.
Partitioning the Customer table in the Contact example
TheCustomer table can be partitioned using the rep_key value as a
subscriptioncolumn. A publication that includes the SalesRep and
Customertables 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
TheContact table must also be partitioned among the sales representatives,
butcontains no reference to the sales representative rep_key value.
Adda subscr iption-list
column Tosolve this problem in Adaptive Server Enterprise, you must add a column
tothe Contact table containing a comma-separated list of subscription
valuesto the row. ( In the present case, there can only be a single
subscriptionvalue.) The column can be maintained using triggers, so that
applicationsagainst the database are unaffected by the presence of the
column. Wecall this column a subscription-list column.
Whena row in the Customer table is inserted, updated or deleted, a trigger
updatesrows in the Contact table. In particular,the trigger updates the
subscription-listcolumn. As the Contact table is marked for replication, the
beforeand after image of the row is recorded in the log.
152