Error in trigger: table X is mutating, trigger/function may not see it


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?

FOR EACH ROW  WHEN (new.APL_CO='01' or new.apl_co='40') BEGIN 
             9,               --EVENT_ID
             1,                     --EVENT_NO
              SYSDATE(),              --ACTION_DATE
              'EVENTS',               --ACTION_USER
              '',                     --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,           
              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>'),              
              bxs_name,                 --X_REF_7
              poh_pla_no2 || ' - ' || pla_name,                     --X_REF_8
              to_char(poh_order_date, 'dd/mm/yyyy') order_date,                     
              :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
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 
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 
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';

Best Answer


  • 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.