Reps.
Whena customer is reassigned to a new sales rep, the Customer table is
updated. The UPDATEis replicated as an INSERT or a or a DELETE to the
oldand new sales representatives, respectively, so that the customer row is
properlytransferred to the new sales representative.
☞Forinformation on the way in which Adaptive Server Anywhere and
SQLRemote work together to handle this situation, see “Who gets what?”
onpage 86.
Whena customer is reassigned, the Contact table is unaffected. Thereare
nochanges to the Contact table, and consequently no entries in the
transactionlog pertaining to the Contact table. In the absence of this
information,SQL Remote cannot reassign the rows of the Contact table
alongwith the Customer.
Thisfailure will cause referential integrity problems: the Contacttable at
theremote database of the old sales representative contains a cust_key value
forwhich there is no longer a Customer.
Usetr iggers to maintain
Contacts Thesolution is to use a trigger containing a special form of UPDATE
statement,which does not make any change to the database tables, but which
doesmake an entry in the transaction log. This log entry contains the before
andafter values of the subscription expression, and so is of the proper form
forthe Message Agent to replicate the rows properly.
Thetrigger must be fired BEFORE operations on the row. In this way, the
BEFOREvalue can be evaluated and placed in the log. Also, the trigger
mustbe fired FOR EACH ROW rather than for each statement, and the
informationprovided by the trigger must be the new subscription expression.
TheMessage Agent can use this information to determine which subscribers
receivewhich rows.
Triggerdefinition Thetrigger definition is as follows:
CREATE TRIGGER UpdateCustomer
BEFORE UPDATE ON Customer
REFERENCING NEW AS NewRow
OLD as OldRow
FOR EACH ROW
BEGIN
// determine the new subscription expression
// for the Customer table
UPDATE Contact
PUBLICATION SalesRepData
OLD SUBSCRIBE BY ( OldRow.rep_key )
NEW SUBSCRIBE BY ( NewRow.rep_key )
WHERE cust_key = NewRow.cust_key;
END;
108