title: Table is mutating, trigger may not see it
body: <p>There is no error when I create the trigger. But when I fire it, an error message displays that the table is mutating. How do I solve this problem of table mutating? </p>
  
  <pre><code>CREATE OR REPLACE TRIGGER tgr_invoice
  AFTER UPDATE OF discharge_date
  ON Admission
  FOR EACH ROW
  DECLARE
  doc_fee NUMBER(5);
  nights_stayed NUMBER(4);
  room_price NUMBER(5);
  room_fee NUMBER(9);
  med_fee NUMBER(7, 2);
  units_prescribed NUMBER(3);
  price_per_unit NUMBER(7, 2);
  total_fee NUMBER(8,2);
  BEGIN
  SELECT consult_fee INTO doc_fee     
  FROM Doctor
  WHERE staff_id IN (SELECT seen_by FROM Admission
  WHERE patient_id = :NEW.patient_id
  AND admission_date = :NEW.admission_date);
  
  SELECT (discharge_date - admission_date) INTO nights_stayed 
  FROM Admission 
  WHERE patient_id = :NEW.patient_id
  AND admission_date = :NEW.admission_date;
  
  SELECT room_price INTO room_price 
  FROM Room
  WHERE room_no IN (SELECT room_no FROM Admission
  WHERE patient_id = :NEW.patient_id
  AND admission_date = :NEW.admission_date);
  
  SELECT units_prescribed INTO units_prescribed 
  FROM Prescription
  WHERE patient_id = :NEW.patient_id
  AND prescription_date = :NEW.admission_date;
  
  SELECT price_per_unit INTO price_per_unit 
  FROM Medicine
  WHERE med_id IN (SELECT med_id FROM Prescription 
  WHERE patient_id = :NEW.patient_id
  AND prescription_date = :NEW.Admission_date);
  
  room_fee := room_price * nights_stayed;
  med_fee := units_prescribed * price_per_unit;
  total_fee := room_fee + doc_fee + med_fee;
  
  INSERT INTO Invoice VALUES(seq_inv_no.NEXTVAL, :NEW.patient_id, 
  :NEW.admission_date, doc_fee, room_fee, med_fee, total_fee);
  END;
  /
  </code></pre>
  
  <p>When I update the admission table and input the discharge date, an error arises 'table SYSTEM.ADMISSION is mutating, trigger/function may not see it'</p>