I am new to oracle , we have migrated our Sybase db to Oracle11g.
Now we are facing Mutating problem in triggers. To resolve the mutating problem in trigger we have written Compound trigger but our problem is that we want to first fire the BEFORE EACH ROW statement first and then BEFORE STATEMENT trigger in COMPOUND trigger , i don't know whether we can do this or not but i need your suggestion, the reason why i need to do this is that we want to validate the data at ROW level first and once it pass the validation we want to fire the BEFORE statement.
Thank you all in advance
Last edited by hemant_patel12; 10-26-12 at 03:38.
Reason: Removing old example
You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Good judgement comes from experience. Experience comes from bad judgement.
I think you should specify and tell us exactly what you want. A trigger may not be the best solution.
Your trigger seems like an automatic conversion of sqlanywhere code to oracle, but a good choice with sqlanywhere is not necessarily good with oracle.
By the way, in oracle triggers fires in the following order :
If two or more triggers with different timing points are defined for the same statement on the same table, then they fire in this order:
All BEFORE STATEMENT triggers
All BEFORE EACH ROW triggers
All AFTER EACH ROW triggers
All AFTER STATEMENT triggers
You can also simplify a lot your code, for example :
SELECT 1 INTO v_temp
WHERE ( SELECT :NEW.ACTIVE
FROM DUAL ) = 'c';
WHEN OTHERS THEN
IF v_temp = 1 THEN
is almost equivalent ( minus the when others bug ) to :