CODEWITHSUNDEEP
oracle-databasetriggersora-04091
mbk
mbk

Reputation: 1

ORA-04091 Error Keep two tables in sync using triggers

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.

Upvotes: 0

Views: 149

Answers (2)

mbk
mbk

Reputation: 1

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

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 143163

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?

Upvotes: 0

Related Questions