In the following example, three tables store information about the interactions between sales representatives and customers: Customers, Contacts, and SalesReps. Each sales representative sells to several customers. For some customers, there is a single contact, and for other customers there are multiple contacts.
For detailed descriptions of the tables, see Description of Contacts, Customers, and SalesReps tables.
A sales representative subscribes to a publication that provides a copy of the SalesRep table, a copy of the Customers table with the details of the customers assigned to them, and a copy of the Contacts table with the details of the contacts that correspond to their customers. For example, each sales representative subscribes to the following publication:
CREATE PUBLICATION SalesRepData ( TABLE SalesReps TABLE Customers SUBSCRIBE BY rep_key TABLE Contacts SUBSCRIBE BY (SELECT rep_key FROM Customers WHERE Contacts.cust_key = Customers.cust_key ) );
For a detailed description of this publication, see Using disjoint data partitions.
This reassignment of rows among subscribers is sometimes called territory realignment because it is a common feature of sales force automation applications, where customers are periodically reassigned among representatives.
On the consolidated database, when a customer is reassigned to a new sales representative, the rep_key value in the Customers table is updated.
The following statement reassigns a customer, cust1, to another sales representative, rep2.
UPDATE Customers SET rep_key = 'rep2' WHERE cust_key = 'cust1';
This update is replicated:
As a DELETE statement to the Customers table on the old sales representative's remote database.
As an INSERT statement to the Customers table on the new sales representative's remote database.
The Contacts table is not changed. There are no entries in the consolidated database transaction log about the Contacts table. As a result, SQL Remote on the remote databases cannot reassign the cust_key rows of the Contacts table. This inability causes the following referential integrity problem: the Contacts table on the remote database of the old sales representative contains a cust_key value for which there is no longer a customer.
A solution is to use a BEFORE UPDATE trigger. A BEFORE UPDATE trigger does not make any change to the database tables, but does create an entry in the consolidated database transaction log.
This BEFORE UPDATE trigger must be fired:
Before the UPDATE statement is run. so that the BEFORE value of the row is evaluated and added to the transaction log.
FOR EACH ROW rather than for each statement. The information provided by the trigger must be the new subscription expression.
For example, the following statement creates a BEFORE UPDATE trigger.
CREATE TRIGGER "UpdateCustomer" BEFORE UPDATE OF "rep_key" // only fire the trigger when we modify rep_key, not any other column ORDER 1 ON "Cons"."Customers" /* REFERENCING OLD AS old_name NEW AS new_name */ REFERENCING NEW AS NewRow OLD AS OldRow FOR EACH ROW BEGIN // determine the new subscription expression // for the Customers table UPDATE Contacts PUBLICATION SalesRepData OLD SUBSCRIBE BY ( OldRow.rep_key ) NEW SUBSCRIBE BY ( NewRow.rep_key ) WHERE cust_key = NewRow.cust_key; END ;
SQL Remote uses the information placed in the transaction log to determine which subscribers receive which rows.
The consolidated database transaction log contains two entries after this statement is executed:
INSERT and DELETE statements for the Contacts table generated by the BEFORE UPDATE trigger.
--BEGIN TRIGGER-1029-0000461705 --BEGIN TRANSACTION-1029-0000461708 BEGIN TRANSACTION go --UPDATE PUBLICATION-1029-0000461711 Cons.Contacts --PUBLICATION-1029-0000461711-0002-NEW_SUBSCRIBE_BY-rep2 --PUBLICATION-1029-0000461711-0002-OLD_SUBSCRIBE_BY-rep1 --NEW-1029-0000461711 --INSERT INTO Cons.Contacts(contact_key,name,cust_key) --VALUES ('5','Joe','cust1') go --OLD-1029-0000461711 --DELETE FROM Cons.Contacts -- WHERE contact_key='5' go --END TRIGGER-1029-0000461743
The original UPDATE statement that was executed, as well as INSERT and DELETE statements for those users that gained or lost the row respectively.
--PUBLICATION-1029-0000461746-0002-NEW_SUBSCRIBE_BY-rep2 --PUBLICATION-1029-0000461746-0002-OLD_SUBSCRIBE_BY-rep1 --NEW-1029-0000461746 --INSERT INTO Cons.Customers(cust_key,name,rep_key) --VALUES ('cust1','company1','rep2') go --OLD-1029-0000461746 --DELETE FROM Cons.Customers -- WHERE cust_key='cust1' go --UPDATE-1029-0000461746 UPDATE Cons.Customers SET rep_key='rep2' VERIFY (rep_key) VALUES ('1') WHERE cust_key='cust1' go --COMMIT-1029-0000461785 COMMIT WORK
SQL Remote scans the transaction log for the BEFORE and AFTER tags. Based on this information, it can determine which remote users get an INSERT, UPDATE, or DELETE statement.
When a user is in the BEFORE list and not in the AFTER list, then a DELETE statement is sent on the Contacts table.
When a user is in the AFTER list and not the BEFORE list, then an INSERT statement is sent on the Contacts table.
When a user is in both the BEFORE and AFTER lists, nothing is done to the Contacts table but the UPDATE statement on the Customers table is sent.
When the BEFORE and AFTER lists are identical, the remote user already has the row and an UPDATE statement is sent.
In the following example, you must use a BEFORE UPDATE trigger. In other contexts, BEFORE DELETE and BEFORE INSERT are necessary.
UPDATE table-name PUBLICATION pub-name SUBSCRIBE BY sub-expression WHERE search-condition;
In this example, you use a BEFORE trigger.
UPDATE table-name PUBLICATION publication-name OLD SUBSCRIBE BY old-subscription-expression NEW SUBSCRIBE BY new-subscription-expression WHERE search-condition;
The UPDATE statement lists the affected publication and table. The WHERE clause in the statement describes the affected rows. This UPDATE statement does not change the data in the table; it makes entries in the transaction log.
In this example, the subscription expression returns a single value. However, subqueries returning multiple values can also be used. The value of the subscription expression must be the value after the update.
In this example, the only subscriber to the row is the new sales representative. For an example of a row that has existing and new subscribers, see Using overlap partitions.
|Discuss this page in DocCommentXchange.
Send feedback about this page using email.
|Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1|