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…
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).