SQL Remote User's Guide
  SQL Remote Design for Adaptive Server Enterprise
    Partitioning tables that do not contain the subscription column

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 inserted; these rows being identified by the subquery

SELECT contact_key
FROM inserted

An UPDATE trigger for the Contact table 

The trigger for an UPDATE on the Contact table checks to see if the cust_key column is changed, and if it has updates the subscription_list column.

CREATE TRIGGER update_contact_sub_list
ON Contact
FOR UPDATE
AS
IF UPDATE ( cust_key )
   BEGIN
      UPDATE Contact
      SET subscription_list = Customer.rep_key
      FROM Contact, Customer
      WHERE Contact.cust_key=Customer.cust_key
   END

The trigger is written using a join; a subquery could also have been used.

An UPDATE trigger for the Customer table 

The following trigger handles UPDATES of customers, transferring them to a new Sales Rep:

CREATE TRIGGER transfer_contact_with_customer
ON Customer
FOR UPDATE
AS
IF UPDATE ( rep_key )
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 cust_key
      FROM inserted
   )
END