Chapter 7. SQL Remote Design for AdaptiveSer verAnywhere
thatthis subquery can be multi-valued.
Multiple-valued
subqueries The subquery in the BEFORE INSERTtrigger is a UNION expression, and
canbe multi-valued:
...
SELECT rep_key
FROM Policy
WHERE cust_key = NewRow.cust_key
UNION ALL
SELECT NewRow.rep_key
...
♦Thesecond part of the UNION is the rep_key value for the new sales
representativedealing with the customer, taken from the INSERT
statement.
♦Thefirst part of the UNION is the set of existing sales representatives
dealingwith the customer, taken from the Policy table.
Thisillustrates the point that the result set of the subscription query must
beall those sales representatives receiving the row, not just the new sales
representatives.
Thesubquery in the BEFORE DELETE trigger is multi-valued:
...
SELECT rep_key
FROM Policy
WHERE cust_key = OldRow.cust_key
AND rep_key <> OldRow.rep_key
...
♦Thesubquery takes rep_key values from the Policy table. The values
includethe primary key values of all those sales reps who deal with the
customerbeing transferred (WHERE cust_key =OldRow.cust_key),
withthe exception of the one being deleted (AND rep_key <>
OldRow.rep_key).
Thisagain emphasizes that the result set of the subscription query must
beall those values matched by sales representatives receiving the row
followingthe DELETE.
Notes ♦Datain the Customer table is not identified with an individual subscriber
(bya primary key value, for example) and is shared among more than one
subscriber. This allowsthe possibility of the data being updated in more
thanone remote site between replication messages, which could lead to
replicationconflicts. Youcan address this issue either by permissions
(allowingonly certain users the right to update the Customer table, for
example)or by adding RESOLVE UPDATEtriggers to the database to
handlethe conflicts programmatically.
117