sql - Mutuating Tables With Triggers? -

i using before insert or update trigger on table , want query same table in trigger. trigger each row.

when compiled trigger complied without error , warnnings , in valid state. trigger not execute code querying table , after.

all code doing checking if customer has used debitnote#(stored in attribute1) or not. if has used throw error else proceed.

i unable this. mutuatuing table error?.any appreciated.

create or replace trigger apps_appl.xx_ozf_dbtnum_ins_trg   before insert or update    on ozf_claim_lines_all    each row    when (new.org_id = 43) declare    ln_cust_account_id    number;    ln_debit_cust_count   number;    le_claim_invalid      exception;    lv_attribute1         varchar2 (100); begin    --get customer account id new claimid    select cust_account_id      ln_cust_account_id      ozf_claims_all     claim_id = :new.claim_id;     --get count of records customer has same debitnote#    select count (ocla.claim_line_id) ln_debit_cust_count   ozf_claims_all oca, ozf_claim_lines_all ocla      oca.claim_id = ocla.claim_id        , oca.cust_account_id = ln_cust_account_id        , ocla.debitnum = :new.debit_number;        --if count more 0 throw error else proceed    if ln_debit_cust_count > 0          raise le_claim_invalid;    end if; exception    when le_claim_invalid          fnd_message.set_name ('ozf', 'xx_ozf_debitnum_validation');       fnd_msg_pub.add;       raise fnd_api.g_exc_error; end; / 

i guess want make sure there not multiple rows in ozf_claim_lines_all same claim_id , debit_number.

if true can add unique key constraint on table ozf_claim_lines_all on columns claim_id , debitnum.

alter table table_name add constraint constraint_name unique (column1, column2, ... column_n); 

if not option , option using trigger, can work around using row trigger (like 1 have) , instead of select same table can add temp table. use statement trigger select temp table , table want update. can raise error if count > 0.