Synchronizing contacts in the Contact sample

The Contact table contains the name of a person working at a customer company, a foreign key to the customer, and a unique integer identifying the contact. It also contains a last_modified timestamp and a marker to indicate whether the contact is active.

Business rules

The business rules for this table are as follows:

Contact information can be modified at both the consolidated and remote databases.
Each remote database contains only those contacts who work for customers they are assigned to.
When customers are reassigned among sales representatives, contacts must also be reassigned.

Trigger

A trigger on the Customer table is used to ensure that the contacts get picked up when information about a customer is changed. The trigger explicitly alters the last_modified column of each contact whenever the corresponding customer is altered:

CREATE TRIGGER UpdateCustomerForContact
AFTER UPDATE OF rep_id ORDER 1
ON DBA.Customer
REFERENCING OLD AS old_cust NEW as new_cust
FOR EACH ROW
BEGIN
  UPDATE Contact
  SET Contact.last_modified = new_cust.last_modified
  FROM Contact
  WHERE Contact.cust_id = new_cust.cust_id
END

By updating all contact records whenever a customer is modified, the trigger ties the customer and their associated contacts together. Whenever a customer is modified, all associated contacts are modified too, and the customer and associated contacts are downloaded together on the next synchronization.

Downloads

download_cursor The download_cursor script for Contact is as follows:

SELECT contact_id, contact.name, contact.cust_id FROM ( contact JOIN customer ) JOIN salesrep ON contact.cust_id = customer.cust_id AND customer.rep_id = salesrep.rep_id WHERE Contact.last_modified >= ? AND salesrep.ml_username = ? AND Contact.active = 1

This script retrieves all contacts that are active, that have been changed since the last time the sales representative downloaded (either explicitly or by modification of the corresponding customer), and that are assigned to the representative. A join with the Customer and SalesRep table is needed to identify the contacts associated with this representative.

download_delete_cursor The download_delete_cursor script for Contact is as follows:

SELECT contact_id FROM ( Contact JOIN Customer ) JOIN SalesRep ON Contact.cust_id = Customer.cust_id AND Customer.rep_id = SalesRep.rep_id WHERE Contact.last_modified >= ? AND Contact.active = 0

The automatic use of cascading referential integrity by the MobiLink client deletes contacts when the corresponding customer is deleted from the remote database. The download_delete_cursor script therefore has to delete only those contacts marked as inactive.

Uploads

Contact information can be inserted, updated, or deleted at the remote database. The scripts corresponding to these operations are as follows:

upload_insert The following upload_insert script adds a row to the Contact table, marking the contact as active:

INSERT INTO Contact ( contact_id, name, cust_id, active ) VALUES ( ?, ?, ?, 1 )

upload_update The following upload_update script modifies the contact information at the consolidated database:

UPDATE Contact SET name = ?, cust_id = ? WHERE contact_id = ?

Conflict detection is not done on this table.

upload_delete The following upload_delete script marks the contact as inactive at the consolidated database. It does not delete a row.

UPDATE Contact SET active = 0 WHERE contact_id = ?

Discuss this page in DocCommentXchange.