ORA-04091 Error Keep two tables in sync using triggers

Question

I need to keep two tables in a syc so when row inserted in Table A and one of column has certain condition then record should be added in table B. Also when row inserted in Table B then record should be added in Table A. How can this accomplish using Triggers. I had triggers for both tables After Insert but gives ORA-04091 error.

Answer 1

Table is mutating, which means that you are selecting from it at the same time it is being affected by insert (or update). Since 11g, a compound trigger is the way out of it. In earlier Oracle versions (10g and lower) package has been used to deal with it.

However, mutating table frequently raises a question: are you doing it correctly? Maybe code you use can be rewritten so that error is avoided.

Finally, if those two tables contain the same data, why do you have two tables instead of one?

Answer 2

Use compound triggers with checking if record exits for specific condition and then don't go further in trigger to avoid circular dependency.