Sybase SQL Remote DC38133-01-0902-01 User 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
Maintaining the subscription-list column
In order to keep the subscription_list column up to date, triggers are needed
for the following operations:
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.
problem, where customers are assigned to different Sales Reps.
An INSERT trigger for
the Contact table
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:
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