This content has been marked as final. Show 6 replies
What is your 4 digits Oracle version ?
Can you reproduce the problem even in single user mode ?
You should know that "As part of normal processing, Oracle Database rolls back updates, deletes, and merges all the time without telling you that it has done so" (from The Trouble with Triggers http://www.oracle.com/technology/oramag/oracle/08-sep/o58asktom.html). A consequence is that sometimes triggers are executed more than expected and this can lead to problems. This could be an explanation of your problem.
user2511754 wrote:Remove this WHEN OTHERS exception hiding section.
WHEN OTHERS THEN
CompletionStatus := SQLCODE;
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
this can be reproduced even in a single user environment.
Even after removing this , i am able to sucessfuly update the record without any exception and reproduce the same behaviour.
Your problem seems not be related to the trigger restart issue I have already mentioned because you are using a AFTER UPDATE trigger and not a BEFORE UPDATE trigger:
BEFORE Triggers Fired Multiple Times
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.
If you are sure that you update a single row and that your trigger fires twice and if you can easiily reproduce the issue, I recommend that you contact Oracle Support and create a Service Request for your issue that could be an Oracle bug.
Do you mean two records in both CX_HEADER and CX_CHILD table?
How many triggers do you have on the CX_HEADER table?