table is mutating trigger cannot see it

Question

I have a table like so...

create table grades (id number, t1 number, t2 number, t3 number, mark number);

I want a trigger to calculate and update the table with the average mark for each row affected.

so I write a trigger like so...

create or replace trigger rades_trg 
after insert 
on grades
for each row
declare 
v_mark grades.mark%type;
begin
v_mark := (:new.t1 + :new.t2 + :new.t3) / 3;
update grades set mark = v_mark where id = :new.id;   
dbms_output.put_line(v_mark); 
end; 
/

then I enter a new record like so...

insert into grades (id, t1, t2, t3) values (5, 100, 100, 25);

but oracle tells me the table may be mutating and the trigger cannot see it.

Is there any reason you don't want to use a virtual column or a view, instead of storing redundant data? — mustaccio
Hopefully the dbms_output.put_line is just for debugging purpose. Otherwise you may get a ORA-20000: ORU-10027 error. — Wernfried Domscheit

Bob Jarvis - Reinstate Monica Bob Jarvis - Reinstate Monica · Accepted Answer · 2015-12-02T22:06:47

Row triggers are generally not allowed to query or modify the table on which they're declared. To work around this I suggest that you make your trigger a BEFORE trigger so you can simply set :new.mark in your trigger:

create or replace trigger rades_trg 
  before insert on grades
  for each row
begin
  :new.mark := (:new.t1 + :new.t2 + :new.t3) / 3;
  dbms_output.put_line(:new.mark); 
end;

Best of luck.

table is mutating trigger cannot see it

1 Answers