How can you sync 2 tables in oracle via insert trigger without going through an infinite loop?

You have two oracle tables A and B. When you insert a record in table A, that record will be inserted in 开发者_StackOverflowtable B also. When you insert a record in table B, that record will be inserted in table A as well. Now how are you going to accomplish this with a trigger without going through an endless loop? Is there a way to accomplish this with a trigger?

---

Why are you using a trigger? If you need to keep two tables in sync, why wouldn't you create a view named B that queried A? If you need a separate copy of the data because A and B are on different databases, you would be better off using materialized views or even Streams to replicate the data from one server to another.

---

I support Justin's comment that this is probably the wrong way to achieve things. You may find it best to have one table 'C' with two views 'A' and 'B' with INSTEAD OF triggers that maintain the underlying 'C' table.

You can use DBMS_UTILITY.FORMAT_CALL_STACK to determine what is 'higher up' in the call chain. You can also use package level variables or SYS_CONTEXT settings to flag what state your code is in. Neither is very pretty.