SQL & PL/SQL

Hi all,
I'm using oracle 11g. I have an issue with an oracle table. I want if the :new value is already exists in the column , this row to not be inserted. But when i use SELECT statement and WHERE clause with the :NEW value i received/ORA- 04091 - which is normal - i cannot query for a non commited row../. So any ideas how to avoid this? I searched for relevant solutions but most of them allowed the row to be inserted and after that to proceed. Unfortunately, in my case i cannot allow the row to be inserted. On this table i have another trigger - AFTER insert. There i use the new values but i suppose if the BEFORE trigger fails - the other one should never be executed...

Here is what i tried:

create or replace
TRIGGER AR.XX_TO_UPPER 
BEFORE INSERT OR UPDATE ON AR.HZ_PARTIES 
FOR EACH ROW
declare
v_fiscal_code number;

BEGIN

    SELECT JGZZ_FISCAL_CODE
      into V_FISCAL_CODE 
      from HZ_PARTIES
      where JGZZ_FISCAL_CODE = :new.JGZZ_FISCAL_CODE
    ;
    
    if v_fiscal_code is not null then raise_application_error(-20999,'"You cannot insert a duplicate value for jgzz_fiscal_code"');
    end if;

  exception when NO_DATA_FOUND  
    then null;

  

END;

Any ideas?


Thanks in advance,
Bahchevanov.