Chapter 8. SQL Remote Design forAdaptive Ser verEnter prise
inserted;these rows being identiļ¬ed by the subquery
SELECT contact_key
FROM inserted
AnUPDATE trigger for
theContact table Thetrigger for an UPDATE on the Contact table checks to see if the
cust_keycolumn 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
Thetrigger is written using a join; a subquery could also have been used.
AnUPDATE trigger for
theCustomer table Thefollowing trigger handles UPDATES of customers, transferring them to
anew 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
Tuningextraction performance
Whenextracting or synchronizing a user, the
subscription-list
columncan
causeperformance problems as it necessitates a full table scan.
Ifyou are extracting databases for many users, and performance is a problem
foryou, you can use a subscription view toimprove performance. The view
mustcontain a subquery, which is used for extraction and synchronization
only,and is ignored during log scanning. The tables involved still need to
155