On Tuesday, August 30, 2005, 10:11:13 PM, Anderson Miranda wrote:

TABLE_A =>> After update, update TABLE_B
TABLE_B =>> After update, update TABLE_A

> Problem is, if I do such thing using triggers on both tables, I would
> ran into an infinite loop between triggers.

> My question is: How could I avoid the infinite loop and still achieve
> the above mentioned scheme?

I just met a similar situation.

My solution is by creating a table which holds record flag for the
running trigger. The table should have a field TRANSACTION_ID for storing the
CURRENT_TRANSACTION variable.

When the triggers run, it first query the flag table for a
record with transaction_id equal with CURRENT_TRANSACTION.

If it does not exists, the trigger inserts a record in the flag table,
updates the other table and deletes the record in flag table.

If the record does not exist, the trigger should not do anything of
course.


--
Bambang P.


If you're too open minded, your brains will fall out.