♦Thoserows from the Customer table listing customers that deal with the
salesrep subscribed to the data.
Newproblems Themany-to-many relationship between customers and sales representatives
introducesnew challenges in maintaining a proper sharing of information:
♦Wehave a table (in this case the Customer table) that has no reference to
thesales representative value that is used in the subscriptions to partition
thedata.
Again,this problem is addressed by using a subquery in the publication.
♦Eachrow in the Customer table may be related to many rows in the
SalesReptable, and shared with many sales representatives databases.
Putanother way, the rows of the Contact table in “Partitioning tables that
donot contain the subscription expression” on page 105 were partitioned
intodisjoint sets by the publication. Inthe present example there are
overlappingsubscriptions.
Tomeet the replication goals we again need one publication and a set of
subscriptions. In this case, we use two triggers to handle the transfer of
customersfrom one sales representative to another.
The publication
Asingle publication provides the basis for the data sharing:
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
),
);
Thesubscription statements are exactly as in the previous example.
Howthe publication
works Thepublication includes part or all of each of the three tables.To understand
howthe publication works, it helps to look at each article in turn:
♦SalesReptable Thereare no qualifiers to this article, so the entire
SalesReptable is included in the publication.
...TABLE SalesRep,
...
♦Policytable Thisarticle uses a subscription expression to specify a
columnused to partition the data among the sales reps:
114