The problem of territory realignment (reassigning rows among subscribers) requires special attention, just as in the section Territory realignment in the Contacts example.
You need to write triggers to maintain proper data throughout the installation when territory realignment (reassignment of rows among subscribers) is allowed.
This example requires that a customer transfer be achieved by deleting and inserting rows in the Policy table.
To cancel a sales relationship between a customer and a sales representative, a row in the Policy table is deleted. In this case, the Policy table change is properly replicated to the sales representative, and the row no longer appears in their database. However, no change has been made to the Customers table, and so no changes to the Customers table are replicated to the subscriber.
In the absence of triggers, this would leave the subscriber with incorrect data in their Customers table. The same kind of problem arises when a new row is added to the Policy table.
The solution is to write triggers that are fired by changes to the Policy table, which include a special syntax of the UPDATE statement. The special UPDATE statement makes no changes to the database tables, but does make an entry in the transaction log that SQL Remote uses to maintain data in subscriber databases.
Here is a trigger that tracks INSERTS into the Policy table, and ensures that remote databases contain the proper data.
CREATE TRIGGER InsPolicy BEFORE INSERT ON Policy REFERENCING NEW AS NewRow FOR EACH ROW BEGIN UPDATE Customers PUBLICATION SalesRepData SUBSCRIBE BY ( SELECT rep_key FROM Policy WHERE cust_key = NewRow.cust_key UNION ALL SELECT NewRow.rep_key ) WHERE cust_key = NewRow.cust_key; END;
Here is a corresponding trigger that tracks DELETES from the Policy table:
CREATE TRIGGER DelPolicy BEFORE DELETE ON Policy REFERENCING OLD AS OldRow FOR EACH ROW BEGIN UPDATE Customers PUBLICATION SalesRepData SUBSCRIBE BY ( SELECT rep_key FROM Policy WHERE cust_key = OldRow.cust_key AND Policy_key <> OldRow.Policy_key ) WHERE cust_key = OldRow.cust_key; END;
Some of the features of the trigger are the same as in the previous section. The major new features are that the INSERT trigger contains a subquery, and that this subquery can be multi-valued.
The subquery in the BEFORE INSERT trigger is a UNION expression, and can be multi-valued:
... SELECT rep_key FROM Policy WHERE cust_key = NewRow.cust_key UNION ALL SELECT NewRow.rep_key ...
This illustrates the point that the result set of the subscription query must be all those sales representatives receiving the row, not just the new sales representatives.
The subquery in the BEFORE DELETE trigger is multi-valued:
... SELECT rep_key FROM Policy WHERE cust_key = OldRow.cust_key AND rep_key <> OldRow.rep_key ...
This again emphasizes that the result set of the subscription query must be all those values matched by sales representatives receiving the row following the DELETE.
|Send feedback about this page via email or DocCommentXchange||Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0|