Hi,
I found the following statement in mysql trigger which needs to migrated in oracle ,kindly help me to resolve this problem
if exists (select 1 from stakeholder_extids where stakeh_type='RA' and
case when coalesce(ext_acq_id,'oOo')=coalesce(:new.ext_acq_id,'oOo') then 1 else 0 end=1 and
case when coalesce(ext_merchant_id,'oOo')=coalesce(:new.ext_merchant_id,'oOo') then 1 else 0 end=1 and
case when coalesce(ext_store_id,'oOo')=coalesce(:new.ext_store_id,'oOo') then 1 else 0 end=1 and
case when coalesce(ext_device_id,'oOo')=coalesce(:new.ext_device_id,'oOo') then 1 else 0 end=1
) then
insert into trrans_requests_aabbcc values(1);
end if;
Complete code for trigger is
create or replace TRIGGER stakeholder_extid_dirt_i before insert on stakeholder_extids for each row
Declare v_rec_no Number(19);
begin
gen_rec_no('I','stakeholder_extids',v_rec_no);
insert into stakeholder_extidh (action_no ,stakeholder_id ,extid_srno ,stakeh_type ,ext_acq_id ,ext_merchant_id ,ext_store_id ,ext_device_id ,last_upd_on ,geog_id ,last_upd_dtime ,extid_name ,stkhldr_ext_dbit04_flg ) values(v_rec_no ,:new.stakeholder_id ,:new.extid_srno ,:new.stakeh_type ,:new.ext_acq_id ,:new.ext_merchant_id ,:new.ext_store_id ,:new.ext_device_id ,:new.last_upd_on ,:new.geog_id ,:new.last_upd_dtime ,:new.extid_name ,:new.stkhldr_ext_dbit04_flg );
:new.rec_no:=v_rec_no;
Update central_log set rec_no = v_rec_no where action_no = v_rec_no;
if :new.stakeh_type='RA' then
if exists (select 1 from stakeholder_extids where stakeh_type='RA' and
case when coalesce(ext_acq_id,'oOo')=coalesce(:new.ext_acq_id,'oOo') then 1 else 0 end=1 and
case when coalesce(ext_merchant_id,'oOo')=coalesce(:new.ext_merchant_id,'oOo') then 1 else 0 end=1 and
case when coalesce(ext_store_id,'oOo')=coalesce(:new.ext_store_id,'oOo') then 1 else 0 end=1 and
case when coalesce(ext_device_id,'oOo')=coalesce(:new.ext_device_id,'oOo') then 1 else 0 end=1
) then
insert into trrans_requests_aabbcc values(1);
end if;
end if;
End;