How to avoid triggers from firing recursively?
We're using a packaged application on top of oracle 9i. The application makes an entry into a table whenever organization entity A charges an amount to entity B. To be able to follow the flow of these charges in both directions a corresponding entry for entity B has to be made in this table as well. Unfortunately the packaged application doesn't provide this function and we're trying to solve this issue with triggers for this table.
The logic of the trigger is as follows:
Upon insert of record A(Col1,Col2) into table Z, insert an additional record B(Col2, Col1) in table Z.
We created similar triggers to cope with update and delete events.
The syntax and logic of the triggers is fine. Upon execution the trigger raised an error saying it cannot update the table as it is being already updated by the calling DML statement.
There is workaround mentioned for this situation in the Oracle documentation using a temporary table (a row trigger writes for each record to the temporary table; at the end a statement trigger fires which copies those entries back to the original table). We've also tried this but now the problem is that those triggers fire each other iteratively thus creating an infinite loop which only stops upon hitting the cursor limit (at the moment it's 50).
Does anyone experience a similar problem and has a solution for this (short of handling it outside of the database)? Upon interest I can also post the trigger codes.