Retirement Date FUNCTION and TRIGGER

Question

Please I need more help with this function, which is suppose to return a date of retirement that will retrieved by a trigger and update the EDOR"Retirement Date" column on EMPLOYEES_MASTER_DETAILS Table using the value in DOFA"hire_date"

The function and trigger compile successfully. But whenever I update a record I receive this error message...!

1 error has occurred ORA-04091: table DBA_AUWAL.EMPLOYEES_MASTER_DETAILS is mutating, trigger/function may not see it ORA-06512: at "DBA_AUWAL.EDOR_FUNCTION", line 9
ORA-06512: at "DBA_AUWAL.EDOR_FUNCTION", line 17 ORA-06512: at "DBA_AUWAL.EDOR_IN_TRG", line 2 ORA-04088: error during execution of trigger 'DBA_AUWAL.EDOR_IN_TRG'

My function is

create or replace function EDOR_FUNCTION(DOFA in date) 
return date 
is
dofa_date date;
dob_date date;
new_edor_date date;

cursor c1 is
SELECT DOFA
FROM EMPLOYEES_MASTER_DETAILS;

cursor c2 is
SELECT date_of_birth
FROM EMPLOYEES_MASTER_DETAILS;

BEGIN
open c1;
fetch c1 into dofa_date;
close c1;

open c2;
fetch c2 into dob_date;
close c2;

if dofa_date - dob_date <= 25 then new_edor_date := add_months(dofa_date,  35*12);
else new_edor_date := add_months(dob_date, 60*12);
end if;
return  new_edor_date;

END;

This is the Trigger

create or replace TRIGGER EDOR_IN_TRG 
before INSERT OR UPDATE ON EMPLOYEES_MASTER_DETAILS
FOR EACH ROW
BEGIN
:new.EDOR := EDOR_FUNCTION(:new.DOFA);
END;

Try making your trigger an after instead of a before, or move this logic into a Stored Proc. - Reference this question w/ your error — Nick

APC APC · Accepted Answer · 2017-04-05T14:43:42

Mutating trigger happens when the trigger executes DML or query on its owning table. Your function does this.

The good news is the function doesn't need to query EMPLOYEES_MASTER_DETAILS as all the necessary details are available to the trigger:

create or replace function EDOR_FUNCTION
    (p_DOFA in date, p_dob in date) 
return date 
is
    new_edor_date date;
begin
    if p_dofa- p_dob <= 25 then 
        new_edor_date := add_months(p_dofa,  35*12);
    else 
        new_edor_date := add_months(p_dob, 60*12);
    end if;

   return  new_edor_date;

END;

The trigger now looks like this:

create or replace TRIGGER EDOR_IN_TRG 
before INSERT OR UPDATE ON EMPLOYEES_MASTER_DETAILS
FOR EACH ROW
BEGIN
    :new.EDOR := EDOR_FUNCTION(:new.DOFA, :NEW.DOB);
END;

Not only does this fix the ORA-04088 issue it also resolves the gaping whole in your business logic: your original function will fail horribly when you have more than one record in your table.

Retirement Date FUNCTION and TRIGGER

1 Answers