Suppose I have two tables I want to keep in sync:
create table a(x number, s varchar2(10)); create table b(x number, s varchar2(10));
It's easy to keep B synchronized with A, using triggers such as:
create or replace trigger a_t1 after insert on a for each row begin insert into b(x,s) values(:new.x,:new.s); end;
Of course, if I add the inverse trigger:
create or replace trigger b_t1 after insert on b for each row begin insert into a(x,s) values(:new.x,:new.s); end;
I will get a mutating table error if I insert into either table (and which of course is the only thing that keeps the triggers from executing in an infinite cycle).
My question: how can I arrange for these two tables to sync themselves, such that modifications to A are reflected in B, and vice versa, without modifying the applications that are modifying A and B?
This is for a very short-term solution, so no hack is too cheap.
(disclaimer) I realize the horribleness of what I'm asking! This is a short-term integration bridge and will go away as soon as integration is complete. (end disclaimer)