CODEWITHSUNDEEP
oracle-databasetriggersmutation
Ahmad Tarraf
Ahmad Tarraf

Reputation: 89

SQL Error: ORA-04091: table is mutating while updating by a trigger

i am new to oracle , i am developing a hospital management system , i have this table to store patients : 

   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)
);

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 :

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

put when i test the trigger and use an update statement like this :

update patients set p_exit = to_date('01/02/2001','dd/mm/yyyy') where p_id = 2;

and run it i get this error :

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.

can anyone tell me how to fix it ? and thanks so much ..

Upvotes: 0

Views: 1417

Answers (1)

gvenzl
gvenzl

Reputation: 1891

You are modifying the very same table in the trigger that is currently being modified. As they error tells you:

*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.

There is actually no need in updating the table again, you can simply use a virtual column directly on the table that makes the entire trigger redundant:

CREATE TABLE patients(
   p_id NUMBER NOT NULL PRIMARY KEY,
   p_fullname VARCHAR2(255),
   p_gender CHAR(1),
   de_no NUMBER,
   p_entry DATE,
   Diagnosis VARCHAR2(25),
   p_exit DATE,
   RESIDENCY NUMBER GENERATED ALWAYS AS (p_exit-p_entry)
);


insert into patients (p_id, p_fullname, p_gender, de_no, p_entry, diagnosis) values (1, 'GVENZL', 'M', 1234, SYSDATE-1, 'healthy' );
commit;

select p_fullname, residency from patients;

update patients set p_exit = sysdate;
commit;

select p_fullname, residency from patients;

Upvotes: 1

Related Questions