Sybase DC38133-01-0902-01 manual Reps, Properly transferred to the new sales representative, 108

	Reps.
	When a customer is reassigned to a new sales rep, the Customer table is
	updated. The UPDATE is replicated as an INSERT or a or a DELETE to the
	old and new sales representatives, respectively, so that the customer row is
	properly transferred to the new sales representative.
	☞ For information on the way in which Adaptive Server Anywhere and
	SQL Remote work together to handle this situation, see “Who gets what?”
	on page 86.
	When a customer is reassigned, the Contact table is unaffected. There are
	no changes to the Contact table, and consequently no entries in the
	transaction log pertaining to the Contact table. In the absence of this
	information, SQL Remote cannot reassign the rows of the Contact table
	along with the Customer.
	This failure will cause referential integrity problems: the Contact table at
	the remote database of the old sales representative contains a cust_key value
	for which there is no longer a Customer.
Use triggers to maintain	The solution is to use a trigger containing a special form of UPDATE
Contacts	statement, which does not make any change to the database tables, but which
	does make an entry in the transaction log. This log entry contains the before
	and after values of the subscription expression, and so is of the proper form
	for the Message Agent to replicate the rows properly.
	The trigger must be fired BEFORE operations on the row. In this way, the
	BEFORE value can be evaluated and placed in the log. Also, the trigger
	must be fired FOR EACH ROW rather than for each statement, and the
	information provided by the trigger must be the new subscription expression.
	The Message Agent can use this information to determine which subscribers
	receive which rows.
Trigger definition	The trigger 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