Trigger counting rows gives ORA-04091: table is mutating, trigger/function may not see it

Question

I get this error when updating my table

 UPDATE "Username"."EMPPROJECT" 
 SET ENDDATE = TO_DATE('2016-09-11 00:00:00', 'YYYY-MM-DD HH24:MI:SS') 
 WHERE ROWID = 'AAF+8XAAEAAGBIGAAB'
   AND ORA_ROWSCN = '537035617'

Error saving changes to table "Username"."EMPPROJECT": Row 8:
ORA-04091: table Username.EMPPROJECT is mutating, trigger/function may not see it ORA-06512: at "Username.CHECKOVERLAPDATEUPDATE", line 4
ORA-04088: error during execution of trigger 'Username.CHECKOVERLAPDATEUPDATE'

The trigger is defined like this:

create or replace trigger checkOverlapDateUpdate 
before Update on EmpProject
for each row
declare
    countOfOverlap integer;
begin
    select count(*) 
    into countOfOverlap 
    from EmpProject EP 
    where isOverlapping(:new.startDate, :new.endDate, startDate, endDate) = 1 
      and EP.EmpID = :new.EmpId;

    if countOfOverlap > 0 
    then
        RAISE_APPLICATION_ERROR(-00000, 'Overlapping Insertion Dates');
        rollback;
    end if;
end;

Did you google for “is mutating, trigger/function may not see it” ? — Caius Jard, Oct 07 '18 at 04:40
Go through this link. Jeffrey Kemp discusses various options you could use to implement the "Non Overlapping dates constraint" https://jeffkemponoracle.com/2012/08/non-overlapping-dates-constraint/ — Kaushik Nayak, Oct 07 '18 at 05:44
By the way, `TO_DATE('2016-09-11 00:00:00', 'YYYY-MM-DD HH24:MI:SS')` can be written as `date '2016-09-11'`, and `"EMPPROJECT"` should be written as `empproject`. — William Robertson, Oct 07 '18 at 08:29

score 5 · Answer 1 · answered Oct 07 '18 at 07:23

What Oracle is trying to tell you here is this:

An update can span multiple rows.
Your trigger, however, looks at the single row updates (for each row).
As the single row updates of a multiple row update statement occur in arbitrary order, Oracle can not guarantee a consistent, determinate result with the subquery in the trigger.

For example:

update empproject set startdate = startdate + 100, enddate = enddate + 100;

shouldn't be a problem, as all date ranges get shiftet the same amount of days. However, when updating the first row and looking at this alone, there may be a conflict, because the other rows have not changed yet. There may even be two rows only in the table where the intermediate situation would lead to an overlapping date range. Then it would suddenly depend on which of the two rows Oracle picks first for the update. In one case the update would run without an error, in the other case it would crash. A non-deterministic result, which of course is not allowed.

The solution to this is to check the rows after the complete update.

You can write an AFTER UPDATE statement trigger (i.e. AFTER UPDATE ON EmpProject without FOR EACH ROW) and compare each row of the table with all others. On a big table this may be quite costly.

The preferred method is to write a compound trigger. This trigger would have an array for the changed rows (or their keys). In the trigger's AFTER EACH ROW section, you'd add the row to the array, and in the trigger's AFTER STATEMENT section you'd check each of these rows for overlapping ranges.

Compund triggers are described here: https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#LNPLS2005

Trigger counting rows gives ORA-04091: table is mutating, trigger/function may not see it

1 Answers1