intheir database. However,no change has been made to the Customer table,
andso no changes to the Customer table are replicated to the subscriber.
Inthe absence of triggers, this would leave the subscriber with incorrect data
intheir Customer table. The same kind of problem arises when a newrow
isadded to the Policy table.
UsingTriggers to solve
theproblem Thesolution is to write triggers that are fired by changes to the Policy table,
whichinclude a special syntax of the UPDATE statement. The special
UPDATEstatement makes no changes to the database tables, but does make
anentry in the transaction log that SQL Remote uses to maintain data in
subscriberdatabases.
ABEFORE INSERT
trigger Here is a trigger that tracks INSERTSinto the Policy table, and ensures that
remotedatabases contain the proper data.
CREATE TRIGGER InsPolicy
BEFORE INSERT ON Policy
REFERENCING NEW AS NewRow
FOR EACH ROW
BEGIN
UPDATE Customer
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;
ABEFORE DELETE
trigger Here is a corresponding trigger that tracks DELETES from the Policytable:
CREATE TRIGGER DelPolicy
BEFORE DELETE ON Policy
REFERENCING OLD AS OldRow
FOR EACH ROW
BEGIN
UPDATE Customer
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;
Someof the features of the trigger are the same as in the previous section.
Themajor new features are that the INSERT trigger contains a subquery, and
116