Keeping tables synchronized in Oracle

Question

We're about to run side-by-side testing to compare a legacy system with a new shiny version. We have an Oracle database table, A, that stores data for the legacy system, and an equivalent table, B, that stores data for the new system, so for the duration of the test, the database is denormalized. (Also, the legacy system and table A are fixed - no changes allowed)

What I want to do is to allow the infrequent DML operations on A to propagate to B, and vice-versa. I started with a pair of triggers to do this, but hit the obvious problem that when the triggers run, the tables are mutating, and an exception is thrown.

Is there a standard way of handling this issue? I've read differing reports on whether or not using dbms_scheduler is the way to go...

Thanks,

Andy

Update: I've ended up chickening out of the whole issue and ensured that all stored procedures that update A, also update B, and vice versa.

I've marked Quassnoi's answer as accepted, because I'd follow his suggestions if faced with the same issue in the future.

I've marked up JosephStyon's answer, because I briefly got things working by adding two insert/update statement level triggers on tables A and B, then doing his merge procedure using A or B as the master table, depending on which trigger ran (although first I checked that the target table would be changed by the merge, earlying out if not).

Answer 1

I'd create A and B as views over a single normalized (or denormalized) table, and created an INSTEAD OF trigger over these views to handle DML operations.

If the query plans matter, it's better to keep two copies of tables: A_underlying and B_underlying and create the views just like this:

CREATE VIEW A
AS
SELECT  *
FROM    A_underlying

CREATE VIEW B
AS
SELECT  *
FROM    B_underlying

The predicates will be pushed into the views, and the query plans for actual tables and views will be the same.

In INSTEAD OF triggers over both view, you should put the data into both underlying tables.

Answer 2

Do you really mean DDL, not DML?

With DML you can have a look at Oracles Multi Master Replication to keep the tables in synch or you could also have a look at the tool SymmetricDS for this purpose.

With DDL the only solution I'm aware of is again the Oracle advanced replication.