ORA-04091 TABLE ODB.EMPLOYEE IS MUTATING, TRIGGER/FUNCTION MAY NOT SEE IT. IS THERE SOMETHING WRONG WITH MY TRIGGER?

Trying to create a trigger when there is an update of Status on Employee Table and capture some values for the record in Employee table and Employee_Header table for that record and send an email. The trigger throws an error.

CREATE OR REPLACE TRIGGER ODB.TRG_EMPLOYEE_STATUS_EMAIL 
AFTER UPDATE OF STATUS ON ODB.EMPLOYEE 
FOR EACH ROW
DECLARE

  s_message varchar2(4000);
  s_subject varchar2(1000);
  s_return_message varchar2(4000);
  s_database varchar2(50);
  v_rm   EMPLOYEE%ROWTYPE;
  v_sh EMPLOYEE_HEADER%ROWTYPE;

BEGIN
  if :old."STATUS"  = 'HOLD' AND :new."STATUS"  = 'ACTIVE' AND :new."CATEGORY" = 'FULLTIME' then
   
    
select * into v_rm from EMPLOYEE WHERE :new."STATUS"  = 'ACTIVE' AND ROWNUM>1;
select * into v_sh from EMPLOYEE_HEADER WHERE ROWNUM>1;

    s_subject := 'NAME ' || v_rm.NAME ||' message ' || ' CHECK LOG OF EMPLOYEE' || Chr(13) || '   STATUS:  ' || v_rm.STATUS ;
    s_message := 'SAMPLE' || Chr(10)||Chr(13) || 'THE STATUS IN EMPLOYEE_HEADER IS: ' || Chr(10)|| '     STATUS:  ' || v_sh.STATUS ;

    pkg_email.sendEmail('INPUT PARAMETERS TO SEND EMAIL');
  end if;
END;

Answer

You can’t select from a table which is just being changed; it is mutating. Though, as you can use the :new pseudorecord, you can “skip” that error. Also, where rownum > 1 is useless as it is never true. I don’t know what you meant to say by using it.

I see you’ve created columns using double quotes. In Oracle, that’s usually a mistake. Not that it won’t work – it will, but you always have to reference columns using double quotes and match letter case.

Finally, trigger might look like this (read comments within code):

create or replace trigger odb.trg_employee_status_email 
  after update of status on odb.employee 
  for each row
declare
  s_message varchar2(4000);
  s_subject varchar2(1000);
  s_return_message varchar2(4000);
  s_database varchar2(50);
  -- v_rm   employee%rowtype;  -- you don't need that
  v_sh employee_header%rowtype;
begin
  if :old."status"  = 'HOLD' and :new."status"  = 'ACTIVE' and 
     :new."category" = 'FULLTIME' 
  then
    -- You can't select from a table which is just being changed - it is "mutating".
    -- Besides, AND ROWNUM > 1 will never return anything. You can only check
    -- ROWNUM <= some_value
    --select * into v_rm from employee where :new."status"  = 'ACTIVE' and rownum>1;
    
    select * into v_sh from employee_header where rownum>1;

    -- instead of SELECT ... INTO V_RM, use :NEW pseudorecord
    s_subject := 'NAME ' || :new.name ||' message ' || ' CHECK LOG OF EMPLOYEE' 
      || chr(13) || '   STATUS:  ' || :new.status ;
    s_message := 'SAMPLE' || chr(10)||chr(13) || 'THE STATUS IN EMPLOYEE_HEADER IS: ' || chr(10)|| '     STATUS:  ' || v_sh.status ;

    pkg_email.sendemail('INPUT PARAMETERS TO SEND EMAIL');
  end if;

exception 
  when no_data_found then null;
end;