Re: Triggers

On 13.09.2010 16:17, The Magnet wrote:
>
> 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.
>

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 Received on Mon Sep 13 2010 - 19:57:36 CEST