Sybase DC38133-01-0902-01 manual Customer

♦UPDATES on the Policy table have not been described here. They should either be prevented, or a BEFORE UPDATE trigger is required that combines features of the BEFORE INSERT and BEFORE DELETE triggers shown in the example.

Using the Subscribe_by_remote option with many-to-many relationships

When the Subscribe_by_remote option is ON, operations from remote databases on rows with a subscribe by value of NULL or an empty string will assume the remote user is subscribed to the row. By default, the Subscribe_by_remote option is set to ON. In most cases, this setting is the desired setting.

The Subscribe_by_remote option solves a problem that otherwise would arise with some publications, including the Policy example. This section describes the problem, and how the option automatically avoids it.

The publication uses a subquery for the Customer table subscription expression, because each Customer may belong to several Sales Reps:

CREATE PUBLICATION SalesRepData ( TABLE SalesRep,

TABLE Policy SUBSCRIBE BY rep_key, TABLE Customer SUBSCRIBE BY (

SELECT rep_key FROM Policy WHERE Policy.cust_key =

Customer.cust_key

),

);

Marc Dill is a Sales Rep who has just arranged a policy with a new customer. He inserts a new Customer row and also inserts a row in the Policy table to assign the new Customer to himself.

Customer

cust1010

cust_name

Policy

pol2345

cust1010

195

SalesRep

195

Marc Dill

As the INSERT of the Customer row is carried out by the Message Agent at the consolidated database, Adaptive Server Anywhere records the subscription value in the transaction log, at the time of the INSERT.

Later, when the Message Agent scans the log, it builds a list of subscribers from the subscription expression, and Marc Dill is not on the list, as the row in the Policy table assigning the customer to him has not yet been applied. If

118