Hi, I wanted to review this and make sure I understand what Oracle is
dong and if this is possible:

We have a customer order table. There is a BEFORE UPDATE..FOR EACH
ROW on the table. When a given type of product is updated a procedure
is called from the trigger to update other RELATED rows, not the row
which caused the trigger to fire.

So, logic is: Type 1 is updated, procedure is called to update
multiple type 2 records, each record related to that Type 1. Each
update will fire the trigger again. Given that the programming logic
is correct, this is not an issue.

Eventually all the necessary updates will be complete. But, because
there are multiple executions of the trigger being fired, will that
create any problem? I found this documentation and I just wanted to
make sure I understood it. According to my understanding, Oracle can
fire the same trigger multiple times for the same transaction, right?


-----------------------------------------
If an UPDATE or DELETE statement detects a conflict with a concurrent
UPDATE, then Oracle Database performs a transparent ROLLBACK to
SAVEPOINT and restarts the update. This can occur many times before
the statement completes successfully. Each time the statement is
restarted, the BEFORE statement trigger is fired again. The rollback
to savepoint does not undo changes to any package variables referenced
in the trigger. Your package should include a counter variable to
detect this situation.

On 13.09.2010 16:17, The Magnet wrote:
Before designing such trigger, you have to read first on ORA-04091 and
term "mutating tables" (the kind of personal preference - on AskTom)

Best regards

Maxim