User's Manual

Chapter 8. SQL Remote Design for Adaptive Server Enterprise
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
Tuning extraction performance
When extracting or synchronizing a user, the subscription-list column can
cause performance problems as it necessitates a full table scan.
If you are extracting databases for many users, and performance is a problem
for you, you can use a subscription view to improve performance. The view
must contain a subquery, which is used for extraction and synchronization
only, and is ignored during log scanning. The tables involved still need to
155