Oracle 8 Tips   by Burleson Consulting

The Data Warehouse Development Life Cycle

Distributed Oracle Data Warehouses

USING TRIGGERS TO UPDATE SNAPSHOTS

But what about replicated tables that require faster propagation? Oracle version 7.3 offers updatable snapshots, but users of previous releases of Oracle can use database triggers to simulate the realtime propagation of changes from a master table to replicated tables. In the following example, an update trigger is placed on a customer table, and relevant changes will be propagated to the New York branch:

CREATE TRIGGER add_customer
AFTER INSERT ON CUSTOMER
AS

IF :DEPT = ‘NY’ THEN
(INSERT INTO customer@NY
VALUES(:parm1, parm2,:parm3);
);

But, what can we do about rows that are deleted from the customer table? Using the same technique, a delete trigger can be placed on the customer table to remove rows from the replicated tables as follows:

CREATE TRIGGER delete_customer
AFTER DELETE ON CUSTOMER
AS
IF :dept = ‘NY’ THEN
(DELETE FROM customer@NY
SEE CODE DEPOT FOR FULL SCRIPT
);

---

This is an excerpt from "High Performance Data Warehousing", copyright 1997. To learn more about Oracle, try "Oracle Tuning: The Definitive Reference", by Donald K. Burleson.  You can buy it direct from the publisher at 30% off here:
http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm

|