Oracle trigger mutating ...

I have two triggers on the Oracle table which I cannot alter because they are the vendors triggers.  I want to use the FOLLOWING clause such that their triggers always fire first but therein lies the problem.

They are BEFORE INSERT and BEFORE UPDATE triggers supplied by the vendor.  In order to use my triggers after theirs the FOLLOWING clause requires the triggers to be the same "type" of trigger.  (compound trigger could potentially get me from the mutating situation)

Is there a way around PRAGMA AUTONOMOUS_TRANSACTION to get this to work?

I have to update the table that is being updated.....if a new row is entered I have to update the row prior with the new prices.

Any help would be appreciated.


Who is Participating?
Mark GeerlingsDatabase AdministratorCommented:
The usual way to avoid the "mutating table" problem with Oracle triggers is basically what sdstuber suggested, multiple triggers:
1. a "for each row" before insert or update to just save each rowid (or primary or unique key) into either an array or a global temporary table (I prefer the global temporary table option)
2. an "after statement" (insert or update) trigger, with *NO* "for each row" clause that retrieves each rowid (or primary or unique key) from the array or global temporary table, and does the actual processing you want it to do for each record.  This works, because triggers with no "for each row" clause do not have the "mutating table" restriction.

In your case, where you have an existing "for each row" trigger that you can't (or don't want to) modify, it is more complicated.

The only option I can think of requires a slight modification to the vendor's triggers and a new, small, custom PL\SQL package that you create for this purpose.  This package only needs to contain three things:
  1. a public varchar2 package variable with a default value of 'Y';
  2. a function to retrieve the value of this variable;
  3. a procedure to set this variable to either 'N' or 'Y'.

Modify the vendor's trigger(s) to call the function in your package to retrieve the value of the package variable, if it = 'Y' (or null) then execute the normal trigger logic, but if it = 'N', then do nothing.

You also need to create a "before insert or update" trigger on the table with no "for each row" clause, to call the procedure to set this public variable to 'Y' so the vendor's trigger(s) work as expected.

In your "after statement" trigger, before you retrieve the rowids, call the procedure in your new custom package to change the package variable to 'N' (which will then cause the vendor's trigger(s) to do nothing (but *ONLY* for the duration of the current transaction!) then execute whatever logic you want your custom trigger to do.

This does require you to keep a close watch for updates from the vendor.  But, you could build in a check in your triggers to catch a change or update from the vendor that you missed.  Just have your trigger call the function to retrieve the value of this package variable.  It expects it to be 'N'.  If it sees 'Y' (or null), you could have your trigger do a "raise_application_error" that will cause the transaction from the application to be rolled back.  This may not be popular with users, but it would give you a reliable way to detect an update from the vendor that you missed.  Then you would have to add your custom logic back into the vendor's revised trigger, and everything would work again.
create multiple triggers.

first  - in your BEFORE triggers,  store ids in  package variables.
put the FOLLOWING clause on these

then, in an AFTER STATEMENT (not FOR EACH ROW)  iterate through your package variables and process the rows you need.    You will probably need to use collections since an insert or update statement could modify more than one row

this is essentially the same thing you would do with a compound trigger except without the "compound" functionality
cyimxtckAuthor Commented:
You will probably need to use collections since an insert or update statement could modify more than one row

This statement you mean to say:

type ty_rowid is table of ROWID INDEX BY PLS_INTEGER;
v_rowid :-= ty_rowid;  

Now store those there but " since an insert or update statement could modify more than one row"

I have to update the table so how will that not fire the trigger again to get it to mutate?

Even if I use BULK COLLECT/FORALL it will still perform an upate?

Not sure if I understand?
cyimxtckAuthor Commented:
I would never get approval from the business to alter the trigger from the vendor....

What is the real "downside" to just keeping it as an AUTONOMOUS_TRANSACTION??

I have read a ton about the misuse of it but cannot really find examples of SELECT x FROM y WHERE... for conclusive results of "what would go wrong"
Your automonomous_transaction is just that.

It's autonomous,  meaning it's disconnected from the transaction that spawned it.

That means - the data changes you made in the parent update can not be seen by the child transactions.

Also, if your child transaction commits and then the parent fails,  you now have data integrity problems.
If your child does not commit (or rollback), then the autonomous transaction call will fail because it must end before returning control back to the parent.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.