Filter
Top Products
in their database. However, no change has been made to the Customer table,
and so no changes to the Customer table are replicated to the subscriber.
In the absence of triggers, this would leave the subscriber with incorrect data
in their Customer table. The same kind of problem arises when a new row
is added to the Policy table.
Using Triggers to solve
the problem
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.
A BEFORE INSERT
trigger
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 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;
A BEFORE DELETE
trigger
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 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;
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
116