mutating, trigger/function may not see it error while using Trigger

Question

I am having a table bugs_metadata like create table bugs_metadata(REPORT_NAME varchar2(10), WHERE_CLAUSE varchar2(100)); insert into bugs_metadata('test','29603754,29605708,29649865'); I am getting "mutating, trigger/function may not see it" error in below trigger on update of WHERE_CLAUSE column in above table:

create or replace TRIGGER "c_b_c_b_update" AFTER  
    UPDATE ON bugs_metadata
    FOR EACH ROW 
BEGIN
CASE
  WHEN UPDATING('WHERE_CLAUSE') THEN
  IF :NEW.WHERE_CLAUSE is not null THEN 
    insert into bug_data(BUG_NUMBER,SUBJECT)
    select rptno,SUBJECT 
    from rpthead 
    where rptno in (select regexp_substr(:NEW.WHERE_CLAUSE,'[^,]+',1,level) WHERE_CLAUSE  
                    from bugs_metadata t2 
                    connect by regexp_substr(:NEW.WHERE_CLAUSE,'[^,]+',1,level) is not null ) 
      and rptno not in(select bug_number from bug_data);

  END IF;
  END CASE; 
END;

Could you please let me know what is wrong here?

Error message is self-explanatory. Your trigger is on bugs_metadata table and also uses that table INSERT INOT bug_data ... SELECT ... WHERE ... bugs_metadata. — Lukasz Szozda
Could you please suggest a way to overcome this and achieve the same functionality. — Subhash Tiwari
You shouldn't form your query by using bugs_metadata table on which your're trying to construct a trigger. You can replace bugs_metadata with dual. So, consider @Littlefoot 's answer. — Barbaros Özhan

Littlefoot Littlefoot · Accepted Answer · 2019-09-07T08:57:36

DUAL should be used instead of bugs_metadata. No problem in doing that because you're just splitting a column into rows, so no need to use the actual table as the value already exists in :new.where_clause.

See the --> right here --> line, it marks the spot.

create or replace trigger c_b_c_b_update 
  after update on bugs_metadata
  for each row 
begin
  case
    when updating('WHERE_CLAUSE') then
      if :new.where_clause is not null then 
         insert into bug_data(bug_number, subject)
           select rptno, subject 
           from rpthead 
           where rptno in (select regexp_substr(:new.where_clause, '[^,]+', 1, level) 
--> right here -->         from dual
                           connect by regexp_substr(:new.where_clause, '[^,]+', 1, level) is not null
                          ) 
             and rptno not in (select bug_number 
                               from bug_data
                              );
      end if;
  end case; 
end;

mutating, trigger/function may not see it error while using Trigger

1 Answers