Triggers
Date: Mon, 13 Sep 2010 07:17:51 -0700 (PDT)
Message-ID: <3d2583e8-3f06-4779-b3d8-22648ac9dec7_at_g18g2000vbn.googlegroups.com>
[Quoted] 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. Received on Mon Sep 13 2010 - 16:17:51 CEST