1

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)

1
  • 1
    And I assume there is some reason that you can't make b a synonym for a or a view on a? Could b be a materialized view based on a? Aug 29 '15 at 2:50
3

Just keep one table. Then replace the other table with a view on the table. Now you should be able to insert/update/delete a row in either the view or the table. Normally this should work. If the insert/update/delete in the view cause a problem then you can always create triggers on the view that replace insert, delete, and update (create trigger t_x instead of update on view). Check the oracle documentation for more information about the triggers

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.