table EMP is mutating, trigger/function may not see it error

user_14605224 0
Viewed 197

I have a table EMP in my apex oracle database that contains an attribute of salary named sal. I have another table EMPSAL that has 3 attributes named averageSal, minSal, maxSal which are to be updated using triggers whenever any DML operation is performed on the EMP table's sal column. Here is the trigger used for upgrading:

create or replace trigger empsal_update_trigger
AFTER update on emp
for each row
declare
avgSal2 emp.sal%type;
minSal2 emp.sal%type;
maxSal2 emp.sal%type;
begin
select avg(sal), min(sal), max(sal) into avgSal2, minSal2, maxSal2 from emp;
delete from empsal;
insert into empsal values(avgSal2, minSal2, maxSal2);
end;

The insert and delete triggers work fine, but the update one given above gives error whenever A record is updated in EMPSAL. I have tried using before keyword instead of after but it's no use.

triggers plsql oracle sql
1 Answers

You don't need a row level trigger for this case, but use a statement level one. Even no need to use local variable definition through use of INSERT INTO ... SELECT... statement.

So, just remove FOR EACH ROW such as

CREATE OR REPLACE TRIGGER empsal_update_trigger AFTER UPDATE ON emp
BEGIN
  DELETE empsal;
  INSERT INTO empsal
  SELECT AVG(sal), MIN(sal), MAX(sal)
    FROM emp;
END;
/
user_58413060
Related