SQL & PL/SQL

Hi All,

I am getting the “ORA-04091 - mutating table error" when my trigger on a table "fnd_flex_values" fires. As I understand it, this error is occuring because, the users are trying to add rows to this table (it is an after insert trigger) and the trigger is trying to get values from the same table.

Any suggestions on how to get around the mutating table error?

My trigger code :

CREATE OR REPLACE TRIGGER "APPS".ST_BU_PARENT_CC
after insert on APPLSYS.FND_FLEX_VALUES REFERENCING OLD AS OLD NEW AS NEW
for each row
Declare
v_flex_value    varchar2(150) :=null;
v_desc          varchar2(2) := null;
v_createdby     number      :=null;
v_lstupdby      number      :=null;
v_lstupdlogin   number      :=null;
begin
  if inserting then

	select a.last_update_login,a.last_updated_by,a.created_by,a.flex_value,
               rtrim(substr(description,instr(b.description,',')+1,5))
	into v_lstupdlogin,v_lstupdby,v_createdby,v_flex_value,v_desc
	from fnd_flex_values a,
             fnd_flex_values_tl b
        where a.flex_value_id = b.flex_value_id
        and   a.flex_value_set_id = :new.flex_value_set_id
	and   a.flex_value_set_id = 1009635
        and   (a.flex_value like '1%' or a.flex_value like '7%')
	order by flex_value asc;


	insert into applsys.fnd_flex_value_hierarchies
        values(:new.flex_value_set_id,v_desc||'STO',v_flex_value,v_flex_value,sysdate,v_lstupdby,sysdate,v_createdby,null,null,null);

        insert into applsys.fnd_flex_value_norm_hierarchy
        values(:new.flex_value_set_id,v_desc||'STO','P',v_flex_value,v_flex_value,sysdate,v_lstupdby,sysdate,v_createdby,v_lstupdlogin,null,null);

  end if;
  exception
    when no_data_found then
      raise;
end;

Thanks,
Chiru