How do i Solve Oracle Mutation Error from TRIGGER

Question

I Have Two Tables; TBL_EMPDETAILS (empdetails_id, EMP_SALARY) and TBL_SERVICE (empdetails_id, Salary, Date_Appointed). The idea is that when i update the tbl_service (which basically is salary history) it should update TBL_EMPDETAILS to the most recent Salary.

I've created a TRIGGER But i keep getting MUTATION ERROR. From my research i have seen recommended compound triggers but i am unsure. I also tried pragma autonomous_transaction; befor the bgin statement but encountered "DEADLOCK ERROR"

create or replace trigger Update_Salary  
   before insert or update on "TBL_SERVICE" 
   for each row 
declare
x number ;
y number ;
z date ;
m date;


begin 
x := :NEW."SALARY";
y  := :NEW."EMPDETAILS_ID";
z := :NEW."DATE_APPOINTED";
Select max(DATE_APPOINTED) 
into m From TBL_SERVICE Where Empdetails_id = y ;
IF  z >= m 
THEN
update tbl_empdetails Set EMP_SALARY = x Where Empdetails_id = y ;
End If;
commit;
end;

I Expect that when i add a row to the TBL_SERVICE for eg. (empdetails_id, Salary, Date_Appointed) = (100, $500 , 20-Jul-2019) it should update the TBL_EMPDETAILS (empdetails_id, EMP_SALARY) to (100, $500)

Mutation Error -ORA-04091 Deadlock Error -ORA-00060

So i Think the COMPOUND TRIGGER LOOKS LIKE THE ROUTE TO GO... I TRIED CODE BELOW BUT IM STILL MISSING SOMETHING :(

create or replace TRIGGER "RDC_HR".Update_Salary  
  FOR UPDATE OR INSERT ON "RDC_HR"."TBL_SERVICE" 
  COMPOUND TRIGGER 

  m date ;

    AFTER EACH ROW IS
     begin 
      Select max(DATE_APPOINTED) into m From TBL_SERVICE 
      Where Empdetails_id = :NEW."EMPDETAILS_ID" ;
    END AFTER EACH ROW;

    AFTER STATEMENT IS
     BEGIN

    IF  (:NEW."DATE_APPOINTED") >= m   THEN
    update tbl_empdetails Set EMP_SALARY = :NEW."SALARY" 
    Where Empdetails_id = :NEW."EMPDETAILS_ID" ;
    End If; 
   END AFTER STATEMENT;

   end Update_Salary;

Triggers are evil. Move the logic to a procedure and call that from your app. Also you should never be doing a commit in a trigger. — OldProgrammer
Does tbl_empdetails have a trigger on it or are there other triggers on tbl_service? — gmiley
The only other triggers on both tables are those for the ID sequence — anton_428
The new version of the trigger won't work. The problem is that you cannot look at the base table in a row-level trigger. Imagine that you execute an update that modifies 50 rows in the table--Oracle will fire the row-level trigger fifty times, but if you were allowed to query the base table you might see partial work done (e.g. in your max(DATE_APPOINTED) query), and this would be ambiguous. Therefore, Oracle doesn't allow you to execute queries on the base table. — Tad Harrison

Littlefoot Littlefoot · Accepted Answer · 2019-07-25T12:58:58

How about merge?

SQL> create table tbl_empdetails (empdetails_id number, emp_salary number);

Table created.

SQL>
SQL> create table tbl_service (empdetails_id number, salary number, date_appointed date);

Table created.

SQL>
SQL> create or replace trigger trg_biu_ser
  2    before insert or update on tbl_service
  3    for each row
  4  begin
  5    merge into tbl_empdetails e
  6      using (select :new.empdetails_id   empdetails_id,
  7                    :new.salary          salary,
  8                    :new.date_appointed  date_appointed,
  9                    (select max(s1.date_appointed)
 10                     from tbl_service s1
 11                     where s1.empdetails_id = :new.empdetails_id
 12                    ) da
 13             from dual
 14            ) x
 15      on (x.empdetails_id = e.empdetails_id)
 16      when     matched then update set e.emp_salary = :new.salary
 17                              where :new.date_appointed > x.da
 18      when not matched then insert (empdetails_id     , emp_salary)
 19                            values (:new.empdetails_id, :new.salary);
 20  end;
 21  /

Trigger created.

SQL>

Testing:

SQL> -- initial value
SQL> insert into tbl_service values (1, 100, sysdate);

1 row created.

SQL> -- this is now the highest salary
SQL> insert into tbl_service values (1, 200, sysdate);

1 row created.

SQL> -- this won't be used because date is "yesterday", it isn't the most recent
SQL> insert into tbl_service values (1, 700, sysdate - 1);

1 row created.

SQL> -- this will be used ("tomorrow")
SQL> insert into tbl_service values (1, 10, sysdate + 1);

1 row created.

SQL> -- a new employee
SQL> insert into tbl_service values (2, 2000, sysdate);

1 row created.

SQL>

The final result:

SQL> select * From tbL_service order by empdetails_id, date_appointed;

EMPDETAILS_ID     SALARY DATE_APPOINTED
------------- ---------- -------------------
            1        700 24.07.2019 15:00:21
            1        100 25.07.2019 15:00:08
            1        200 25.07.2019 15:00:15
            1         10 26.07.2019 15:00:27
            2       2000 25.07.2019 15:00:33

SQL> select * from tbl_empdetails order by empdetails_id;

EMPDETAILS_ID EMP_SALARY
------------- ----------
            1         10
            2       2000

SQL>

How do i Solve Oracle Mutation Error from TRIGGER

2 Answers