Using the Subscribe_by_remote option with many-to-many relationships
Whenthe SUBSCRIBE_BY_REMOTE option is ON, operations that arrive
fromremote databases on rows with a subscribe by value of NULL or ‘’ will
assumethe remote user is subscribed to the row. By default, the
SUBSCRIBE_BY_REMOTEoption is set to ON. In most cases, this setting
isthe desired setting.
TheSUBSCRIBE_BY_REMOTE option solves a problem that otherwise
wouldarise with publications including the Policy example. Thissection
describeshow the option automatically avoids the problem.
Thedatabase uses a subscription-list column for the Customer table, because
eachCustomer may belong to several Sales Reps:
MarcDill is a Sales Rep who has just arranged a policy with a new
customer. He inserts a newCustomer row and also inserts a row in the
Policytable to assign the new Customer to himself. Assumingthat the
subscription-listcolumn is not included in the publication, the operation at
Marc’sremote database is as follows:
Policy
pol2345
cust1010
195
SalesRep
195
Marc Dill
Customer
cust1010
cust_name
Asthe INSERT of the Customer row is carried out by the Message Agent at
theconsolidated database, Adaptive Server Enterprise records the
subscriptionvalue in the transaction log, at the time of the INSERT.
Later,when the Message Agent scans the log, it builds a list of subscribers to
thenew row, using the subscription value stored in the log, and Marc Dill is
noton that list. If SUBSCRIBE_BY_REMOTEwere set to OFF, the result
wouldbe that the new Customer is sent back to Marc Dill as a DELETE
operation.
Aslong as SUBSCRIBE_BY_REMOTE is set to ON, the Message Agent
assumesthat, as the subscription-list column is NULL, the row belongs to
theSales Rep that inserted it. As a result, the INSERTis not replicated back
toMarc Dill, and the replication system is intact.
Youcan use a trigger, which executes after the INSERT,to maintain the
subscription-listcolumn.
164