1

Having looked at the following Trigger, still getting the Row 2 error:

"ORA-04091: table SUMMITTEST.APL_LOG is mutating, trigger/function may not see it"

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;
3
  • 1
    Which part of the error message seems unclear?
    – mustaccio
    Jan 23 '15 at 21:27
  • 1
    You cannot directly access the table that triggers the action inside the body of row level trigger (In your case you're trying to select FROM SUMMITTEST.APL_LOG inside row-level trigger on APL_LOG ) . Common workaround is relatively simple but requires some coding (asktom.oracle.com/pls/asktom/… ) . However, I'd rather not have such a big query inside the trigger body. It's always possible to create a procedure that performs all required actions and use only that procedure for inserting into APL_LOG
    – a1ex07
    Jan 23 '15 at 21:46
  • If you have Oracle 11.2 or newer you can also create a Compound Trigger, there you don't require a package variable Jan 26 '15 at 10:03
1

Explanation:

  • SQL is declarative language, so you can say what has to be done. The database decides how it will be done (i.e. generates an execution plan)

  • SQL is considered to be atomic. Anything DML statement changes happened at the same time.

  • PL/SQL is imperative language, like any other language (C/C++/Java/Pascal)

  • When you create a trigger then your imperative PL/SQL code is called from declarative SQL. And still there is an assumption/requirement that anything DML statement changes is atomic.

    • For example when you insert multiple rows there is no way how to predict which row was inserted as the 1st one. DML statement is atomic, so you should assume that all the rows were inserted at the same time.
  • When using row level locks and constraints it might happen that before trigger might be fired more that once for the same row. And each execution might see different data.

  • Since execution plan can change anytime, you can not predict the order of trigger executions for each row.

So when you put everything together the mutating table error message should be read as:

"I can not guarantee that you code is deterministic."

So multiple executions on the same data might give different results based on execution plan and concurency. At this moment every developer should be cautious, because quick and dirty workarounds lead to data inconsistency issues. This the reason why Oracle forbids you to read the table which is beeing modified by your underlying SQL statement.

-2

That statement should be encapsulated in a package for several reasons:

  • keeps the trigger code easy to understand
  • allows reuse
  • allows a little trick I have used myself: create a job that calls the packaged procedure using dynamic sql. The job is considered to be a separate process and gets around the mutating table error.
  • when you encounter this error you should also examine all the triggers on both tables to see if they are interacting
2
  • 1
    Your trick is really bad advice. Oct 26 '15 at 21:58
  • 1
    @Colin'tHart Well why not add a better answer then?
    – kevinskio
    Oct 26 '15 at 23:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.