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
select count(*) into cated from table_ex
WHERE field1=1 and field2=1 and field3=pdi;
if cated >0 then
'It's wrong!! '
CREATE OR REPLACE TRIGGER trigger_ex
of field2,field1 ON table_ex
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
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
That's it! Good luck!