Having looked at the following Trigger, still getting the Row 2: "ORA-04091: table SUMMITTEST.APL_LOG is mutating, trigger/function may not see it" error, Please can anyone provide any help in resolving this?
create or replace TRIGGER SUMMITTEST.NEW_APP_TEST AFTER INSERT ON SUMMITTEST.APL_LOG
FOR EACH ROW WHEN (new.APL_CO='01' or new.apl_co='40') BEGIN
INSERT INTO SUMMITTEST.WEPALERT select
9, --EVENT_ID
1, --EVENT_NO
SYSDATE(), --ACTION_DATE
'EVENTS', --ACTION_USER
DECODE(JTT_EXTRA1,'','cjohnson300@hotmail.com',jtt_extra1), --COMM_X_1
'', --COMM_X_2
'', --COMM_X_3
'', --COMM_X_4
'', --COMM_X_5
'', --COMM_X_6
'', --COMM_X_7
'', --COMM_X_8
'', --COMM_MESSAGE
'', --COMM_PRIORITY
:new.APL_COMMENT, --COMM_SUBJECT
:new.APL_CO, --X_REF_1
trim(substr(:new.apl_source_key,3,16)) || ' - ' || cla_name contract, --X_REF_2
to_char(:new.apl_value,'99,999,999.99') newnum, --X_REF_3
substr(:new.apl_source_key,-8,8) order2, --X_REF_4
decode(olr_url,'','','This is an amended order. To see the previously approved order, click'), --X_REF_5
decode(olr_url,'','',' <a href=' || olr_url || '>here</a>'), --X_REF_6
bxs_name, --X_REF_7
poh_pla_no2 || ' - ' || pla_name, --X_REF_8
to_char(poh_order_date, 'dd/mm/yyyy') order_date, --X_REF_9
:new.apl_value, --N_REF_1
:new.apl_co, --N_REF_2
0, --N_REF_3
0, --N_REF_4
0, --N_REF_5
0, --N_REF_6
0, --N_REF_7
0, --N_REF_8
0, --N_REF_9
SYSDATE(), --D_REF_1
SYSDATE(), --D_REF_2
SYSDATE(), --D_REF_3
SYSDATE(), --D_REF_4
SYSDATE(), --D_REF_5
SYSDATE(), --D_REF_6
SYSDATE(), --D_REF_7
SYSDATE(), --D_REF_8
SYSDATE(), --D_REF_9
0, --EXCLUSIVE_PROC
0, --ALERT_PROC
0, --NOT_COMM_COUNT
0 --POOL_ID
FROM SUMMITTEST.APL_LOG
left join
(select apr_co, apr_stage, apr_user from SUMMITTEST.apr_rule where apr_type='PO' and apr_subtype=' ' and substr(apr_user,1,1)='%') c on
c.apr_co=apl_co and apl_stage+1=apr_stage
left join
(select ana_co, ana_type,ana_no, jtt_name, jtt_extra1 from SUMMITTEST.ana_anal left join SUMMITTEST.jtt_tab on jtt_co=ana_co and ana_code=jtt_no) d
on d.ana_co=apl_co and d.ana_no=trim(substr
(apl_source_key,3,16)) and d.ana_type=substr(apr_user,-3,3)
left join SUMMITTEST.poh_hfm on poh_co=apl_co and poh_contract=trim(substr(apl_source_key,3,16)) and poh_order2=trim(substr(apl_source_key,-8,8))
left join SUMMITTEST.bxs_sec on bxs_menu=poh_buyer and bxs_type='U'
left join SUMMITTEST.cla_acc on cla_co=apl_co and cla_no=trim(substr(apl_source_key,3,16))
left join SUMMITTEST.pla_acc on pla_co=poh_co and poh_pla_no2=pla_no
left join
(select olr_url, olt_id from SUMMITTEST.olr_res
right join
(select max(olt_res_id) link_me, olt_id from SUMMITTEST.olt_tre group by olt_id) a on a.link_me=olr_id) b on poh_ole=b.olt_id
WHERE :new.apl_id=apl_id and :new.apl_time=apl_time and (:new.apl_stat_x =' ' or :new.apl_stat_x='N') and :new.apl_type='PO';
END;
Aucun commentaire:
Enregistrer un commentaire