Sybase DC38133-01-0902-01 Maintaining the subscription-list column, For the following operations

	Contact			Customer
contact	cust_key	subscription		cust_key	rep_key
_key			_list
con1	cust101		rep1	cust101	rep1
con2	cust101		rep1	cust102	rep1
con3	cust102		rep1	cust103	rep2
con4	cust103		rep2	cust104	rep3
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 trigger for an INSERT on the Contact table sets the subscription_list
the Contact 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