Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Autonomous Transaction Triggers

rvstuckeMay 27 2016

There are many times I have had to create an insert or update trigger with a cursor, which contains the same table I am inserting/updating.  I need it to satisfy a complex constraint, to ensure data integrity.  The problem is, in certain situations I get a "mutating trigger" error telling me the result of the cursor could change based on what I'm inserting/updating in my table.  I end up having to create a complex set of tables which would then be used to crosscheck, through foreign key constraints, what I am changing.  This works because I'm not changing data in these tables until AFTER I change the table I'm in the midst of changing through a trigger which fires after I write.  In plsql there is a execution parameter, AUTONOMOUS TRANSACTION, which executes in an isolated session DML operations and a commit, without committing my current sessions DML activity.

My solution would be to extend that parameter to triggers, which would, 'isolate' the DML currently being executed in my session from the cursors being executed inside the trigger, via execution as though it's a different session, which until commit, doesn't even see my changes.

Comments
Post Details
Added on May 27 2016
5 comments
177 views