updating on same table trigger creation

 CREATE OR REPLACE TRIGGER oa_th_fa.upd_all_comp_periods_finyr

AFTER

 UPDATE

ON oa_th_fa.cm_finyr_periods

REFERENCING NEW AS NEW OLD AS OLD

FOR EACH ROW

Declare

-- added by SMCN

v_comp_code varchar2(6);

v_curr_id number;

v_sup_id number;

v_ap_ar_type varchar2(2);

v_cnt number;

pragma AUTONOMOUS_TRANSACTION;

Begin

if updating  and :new.active_yn <> :old.active_yn then

Begin

update  cm_finyr_periods set active_yn = :new.active_yn

where (finyr_id,finyr_period_id) in (select finyr_id,finyr_period_id from cm_finyr_periods 

                                     where period_no = :old.period_no and finyr_code = :old.finyr_code

                                     --and finyr_id <> 1410

                                     and finyr_period_id <> :old.finyr_period_id);

Exception when others then

  raise_application_error(-20010,'Error at closing all companies periods '||sqlerrm);

End;

End If;

--commit;

End;