title: SQL Error: ORA-04091: table is mutating while updating by a trigger
body: <p>i am new to oracle , i am developing a hospital management system , i have this table to store patients : </p>
  
  <pre><code>   create table patients(
  p_id number not null primary key,
  p_fullname full_name_ty,
  p_gender char,
  de_no number,
  p_entry date ,
  Diagnosis varchar2(25),
  
  p_exit date,
  constraint pdf foreign key (de_no) references department(dep_no)
  );
  </code></pre>
  
  <p>where p_entry is the date when a patient enters the hospital , i made a trigger that calculates the residency time in the hospital on after the upadate of the (p_exit) date for the patient (setting this date means that the patient has left the hospital) , the trigger will simply calculate the difference between the two dates , and print it  , here is the code of the trigger :</p>
  
  <pre><code>create or replace
  trigger period_trig before  update of p_exit on patients for each row
  DECLARE             
  period Number(3);
  enterr DATE;
  exitt DATE;
  BEGIN
  enterr := :old.P_ENTRY;
  exitt:= :NEW.P_EXIT;
  Period :=exitt-enterr; 
  DBMS_OUTPUT.PUT_LINE('Duration:'||period);
  update patients SET RESIDENCY= Period where P_ID = :old.P_ID;
  end period_trig
  </code></pre>
  
  <p>put when i test the trigger and use an update statement like this :</p>
  
  <pre><code>update patients set p_exit = to_date('01/02/2001','dd/mm/yyyy') where p_id = 2;
  </code></pre>
  
  <p>and run it i get this error :</p>
  
  <pre><code>Error starting at line 1 in command:
  update patients set p_exit = to_date('01/02/2001','dd/mm/yyyy') where p_id = 2
  Error report:
  SQL Error: ORA-04091: table SEM.PATIENTS is mutating, trigger/function may not see it
  ORA-06512: at "SEM.UPDATEPAT", line 5
  ORA-06512: at "SEM.PERIOD_TRIG", line 10
  ORA-04088: error during execution of trigger 'SEM.PERIOD_TRIG'
  04091. 00000 -  "table %s.%s is mutating, trigger/function may not see it"
  *Cause:    A trigger (or a user defined plsql function that is referenced in
             this statement) attempted to look at (or modify) a table that was
             in the middle of being modified by the statement which fired it.
  *Action:   Rewrite the trigger (or function) so it does not read that table.
  Error starting at line 1 in command:
  update patients set p_exit = to_date('01/02/2001','dd/mm/yyyy') where p_id = 2
  Error report:
  SQL Error: ORA-04091: table SEM.PATIENTS is mutating, trigger/function may not see it
  ORA-06512: at "SEM.PERIOD_TRIG", line 11
  ORA-04088: error during execution of trigger 'SEM.PERIOD_TRIG'
  04091. 00000 -  "table %s.%s is mutating, trigger/function may not see it"
  *Cause:    A trigger (or a user defined plsql function that is referenced in
             this statement) attempted to look at (or modify) a table that was
             in the middle of being modified by the statement which fired it.
  *Action:   Rewrite the trigger (or function) so it does not read that table.
  </code></pre>
  
  <p>can anyone tell me how to fix it ? and thanks so much ..</p>