Get rid of ORA-04091 table owner.table_name is mutating, trigger/function may not see it.

I won't show how you can get into this error, i am sure you know, because you are here. Briefly, if you make a trigger against some table and you rely in the body of the trigger on the same table data, not to mention if you try to modify data on that table, the above mentioned error will show up.

The workarounds are about three and i will concentrate here only on one of them: using PRAGMA AUTONOMOUS_TRANSACTION and a procedure invoked by the trigger.

So, the code will be:

create or replace procedure proc_ex
(pdi in number) as
pragma autonomous_transaction;
cated number;
begin
select count(*) into cated from  table_ex
            WHERE   field1=1 and field2=1 and field3=pdi;
if cated >0 then
    Raise_application_error (
      -20300,
      'It's wrong!! ' 
    );            
end if;
end;
/            

CREATE OR REPLACE TRIGGER trigger_ex
before update
 of field2,field1 ON table_ex  
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare 
exc exception;
cate number;
BEGIN
if (:old.field1=0 or :old.field2=0) and (:new.field1=1 AND  :new.field2=1) then --verify if any of the two fields was 
                     --changed and the both are 1, after the 
                     --change
    proc_ex (:new.field3);
end if;    
END ;
/

That's it! Good luck!