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

oracletrigger

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;

Best Answer

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.

Related Solutions

NOT getting a mutating table error in trigger

There is an exception. When you define a before insert, row-level trigger on a table and issue a single row INSERT statement, the table is mutating error will not be raised. But if you define the same kind of trigger and issue a multi-row INSERT statement, the error will be raised. Here is an example:

SQL> create table TB_TR_TEST(
  2    col1 number,
  3    col2 number
  4  )
  5  ;

Table created

SQL> create or replace trigger TR_TB_TR_TEST
  2  before insert on TB_TR_TEST
  3  for each row
  4  begin
  5    SELECT max(col1) + 1 INTO :NEW.col1
  6      FROM TB_TR_TEST;
  7    UPDATE TB_TR_TEST SET col2 = 5000;
  8  end;
  9  /

Trigger created

Here is a single-row insert statement, which won't raise mutating table error:

SQL> insert into TB_TR_TEST(col1, col2) values(1,2);

1 row inserted

SQL> insert into TB_TR_TEST(col1, col2) values(3,5);

1 row inserted

SQL> commit;

Commit complete

Here is a multi-row insert statement, which will raise mutating table error:

SQL> insert into TB_TR_TEST(col1, col2)
  2    select 1, 2
  3      from dual;

insert into TB_TR_TEST(col1, col2)
  select 1, 2
    from dual

ORA-04091: table HR.TB_TR_TEST is mutating, trigger/function may not see it
ORA-06512: at "HR.TR_TB_TR_TEST", line 2
ORA-04088: error during execution of trigger 'HR.TR_TB_TR_TEST'

Error in trigger: table is mutating

Replace

select portfoliono into mportfoliono from sipholder where accno=:new.accno;

with

mportfoliono := new.portfoliono;

no need to use a SELECT for values of the updated row.

Best Answer

Related Solutions

NOT getting a mutating table error in trigger

Error in trigger: table is mutating

Related Question