asked on

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.

Thanks,

B

Sean Stuber

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

cyimxtck

ASKER

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?

ASKER CERTIFIED SOLUTION

Mark Geerlings

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial