User's Manual

contact
_key
cust_key
subscription
_list
Contact
cust_key
Customer
rep_key
cust101 rep1
cust102
cust103
rep1
rep2
cust104 rep3
con1 cust101 rep1
cust101con2 rep1
cust102con3 rep1
con4 cust103 rep2
con5 cust104 rep3
☞ For an Adaptive Server Anywhere consolidated database, the solution is
different. For more information, see “Partitioning tables that do not contain
the subscription expression” on page 105.
Maintaining the subscription-list column
In order to keep the subscription_list column up to date, triggers are needed
for the following operations:
♦ INSERT on the Contact table.
♦ UPDATE on the Contact table.
♦ UPDATE on the Customer table.
The UPDATE of the Customer table addresses the territory realignment
problem, where customers are assigned to different Sales Reps.
An INSERT trigger for
the Contact table
The trigger for an INSERT on the Contact table sets the subscription_list
value to the corresponding rep_key value from the Customer table:
CREATE TRIGGER set_contact_sub_list
ON Contact
FOR INSERT
AS
BEGIN
UPDATE Contact
SET Contact.subscription_list = (
SELECT rep_key
FROM Customer
WHERE Contact.cust_key = Customer.cust_key )
WHERE Contact.contact_key IN (
SELECT contact_key
FROM inserted
)
END
The trigger updates the subscription_list column for those rows being
154