How to Avoid "Table Mutating" Errors

How to avoid mutating table error while using a trigger?

Something like this will defer the processing to AFTER STATEMENT level and thus allow to you run queries

create or replace 
trigger avoid_mutate
for insert or update on table_b
compound trigger

  l_station sys.odcivarchar2list := sys.odcivarchar2list();
  l_state   sys.odcivarchar2list := sys.odcivarchar2list();

  before each row is
  begin
    l_station.extend;
    l_state.extend;
    l_station(l_station.count) := :new.station;
    l_state(l_state.count)     := :new.state;
  end before each row;

  after statement is
  declare
    nb_stations_null number;
  begin
    for i in 1 .. l_station.count loop
      SELECT COUNT(1)
      INTO nbr_stations_null
      FROM "TABLE_B"
      WHERE "TABLE_B".STATE = l_state(i) AND
            "TABLE_B".STATION <> l_station(i) AND
            "TABLE_B".DATE_SENT IS NULL;    
            
    IF (nb_stations_null = 0) THEN
        INSERT INTO "TABLE_A" VALUES (l_station(i), l_state(i));
    END IF;
    
  end after statement;

end;
/

How to avoid table mutating errors

Short answer - no trigger, no mutating.

---

Yow can use the trigger with pragma autonomous_transaction for counting of remaining diagnoses for certain patient, but it's is not recommended way to do this.
Better you create new function or procedure to implement your logic on deleted diagnosis. Something like this:

create table Diagnosis as select 456 idDiseases, 123 di_patient from dual;
/
create table diagnosisCount as select 1 numDiseases, 123 di_patient from dual;
/
create table Patient as select 123 Pat_Person, 1 Pat_Sick from dual;
/
drop trigger di_patmustbewell;

create or replace function deleteDiagnosis(idDiseases number) return number is
    rows_ number;
    di_patient number;
    Numdiseases number;
begin
    <<del>> begin 
        delete Diagnosis where IdDiseases = deleteDiagnosis.IdDiseases
        returning Diagnosis.di_patient into deleteDiagnosis.di_patient
        ;
        rows_ := sql%rowcount;
        if rows_ != 1 then raise too_many_rows; end if;
    end del;
    select count(1) into deleteDiagnosis.numDiseases from Diagnosis where Di_Patient = deleteDiagnosis.di_patient;
    if deleteDiagnosis.numdiseases = 0 then <<upd>> begin 
        update Patient set Pat_Sick = 0 where Pat_Person = deleteDiagnosis.di_patient;
        exception when others then 
            dbms_output.put_line('Cannot update Patient di_patient='||di_patient);
            raise;
    end upd; end if;
    return rows_;
end;
/
show errors

declare rows_ number :=  deleteDiagnosis(456);
begin dbms_output.put_line('deleted '||rows_||' rows'); end;
/

deleted 1 rows

select * from Patient;
PAT_PERSON   PAT_SICK
---------- ----------
       123          0

An alternative solution, if you prefer (or must) to use a trigger in your application - declare internal function returning count of patient's diagnoses in the trigger body:

create or replace trigger di_patmustbewell
after delete on diagnosis for each row
declare
    numdiseases number;
    function getNumDiagnosis (di_patient number) return number is
        ret number;
        pragma autonomous_transaction;
    begin
        select count(1) into ret from diagnosis where di_patient = getNumDiagnosis.di_patient;
        return ret;
    end getNumDiagnosis;    
begin
    numDiseases := getNumDiagnosis(:old.di_patient);
    if(numdiseases = 0) then
        update patient set pat_sick = 0 where pat_person = :old.di_patient;
    end if;
end;
/
show errors;

Trigger DI_PATMUSTBEWELL compiled

Hope it helps you a bit.

How to fix mutating table error in trigger?

You cannot reference table ITEM in this trigger because it causes your error. Instead of using SELECT statement use new/old parameters. Try this version of the trigger.

create or replace trigger beforeItem
  before update on item
  for each row 
begin
  -- if :new.qty is not null then
    update stock_item set 
      -- logic to maintaint if no changes on qty field were done
      stock_Qty = stock_Qty - ( nvl(:new.qty,0) - nvl(:old.qty,0) ) 
      where no=:new.no and itemName=:new.name; 
  -- end if;
end;

Mutating table trigger error upon deleting from a table

If we assume that this fiddle is a reproducible test case that demonstrates your problem (note that I had to make several changes to your code in order to be able to do things like inserting the test data), the issue is that the foreign key constraint on the login table is defined to do a cascade delete (on delete cascade). That means that when you are deleting a row from employee, login will be mutating (Oracle is in the middle of deleting the child row). Thus you can't query it from within a trigger on employee.

Depending on exactly what you are hoping to accomplish, you have a few options

1. Don't query the login table and don't store the employeeID in the transaction table. If you have the username that performed the operation in the transaction table, you can always look up their employeeID.
2. Don't use triggers to populate the transaction table. If you have a stored procedure that deletes an employee, it makes more sense for that procedure to do the work of logging the transaction.
3. Don't define your constraints as on delete cascade. That would mean, though, that you'd need to delete the login row prior to deleting the employee row. Here is an example where we remove the on delete cascade from the LOGIN_EMPLOYEEID_FK constraint and add a statement to delete the associated login row.

Table is mutating on BEFORE DELETE trigger

You have hit error ORA-04091.

You can’t query the table that caused a trigger to fire from within the trigger itself. This is by design in Oracle, to protect transaction integrity.

As explained in this other SO post, one solution is to use autonomous transaction. This works by adding a specific pragma in the DECLARE section of your trigger code ; the trigger operations happen in a separate database transaction, that you must commit at the end of the trigger code, like :

CREATE TRIGGER
    ...
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    ...
    COMMIT;
END;

Mutating table error when trying to Insert into Table with select

I wouldn't say that it is insert that makes the difference. If you run the first insert (which "works OK") twice, you'd - I believe - get the same error.

It is probably because trigger code selects from the pozycje_dokumentow, the same table that is affected by insert so it is mutating.

I guess you'll have to rewrite the trigger (or change the way you're doing the whole thing).

---

A sequence approach which will, hopefully, fix your problems.

create sequence seqa;

create or replace trigger trg_bi_podok 
  before insert on pozycje_dokumentow
  for each row
begin
  :new.id := seqa.nextval;
end;

---

As of "unique PK per document": there is a way to do that. Here's a sample code you might use (i.e. adjust to your situation) - it requires an autonomous transaction function which locks the table that contains PK values, fetches the next PK number and releases the table.

CREATE TABLE EVIDENCIJA_BROJ
(
  DP        NUMBER(4)                           NOT NULL,
  REDNI_BR  NUMBER                              NOT NULL,
  WHAT      VARCHAR2(10 BYTE),
  GODINA    NUMBER(4)
);

   FUNCTION f_get_evidencija_broj (par_dp       IN NUMBER,
                                   par_what     IN VARCHAR2 DEFAULT 'EVID',
                                   par_godina   IN NUMBER DEFAULT NULL)
      RETURN NUMBER
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
      l_redni_br   evidencija_broj.redni_br%TYPE;
   BEGIN
          SELECT b.redni_br + 1
            INTO l_redni_br
            FROM evidencija_broj b
           WHERE     b.dp = par_dp
                 AND (   b.godina = par_godina
                      OR par_godina IS NULL)
                 AND b.what = par_what
      FOR UPDATE OF b.redni_br;

      UPDATE evidencija_broj b
         SET b.redni_br = l_redni_br
       WHERE     b.dp = par_dp
             AND b.what = par_what
             AND (   b.godina = par_godina
                  OR par_godina IS NULL);

      COMMIT;
      RETURN (l_redni_br);
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         LOCK TABLE evidencija_broj IN EXCLUSIVE MODE;

         INSERT INTO evidencija_broj (dp,
                                      godina,
                                      what,
                                      redni_br)
              VALUES (par_dp,
                      par_godina,
                      par_what,
                      1);

         COMMIT;
         RETURN (1);
   END f_get_evidencija_broj;

SQL Table is mutating... Error

There is no reliable way to enforce this kind of constraint using triggers. One possible approach is to use a materialized view that automatically refreshes on commit and has a check constraint enforcing your business rule:

create table roomservices (
  pk number not null primary key,
  roomnumber number);

create materialized view mv_roomservices  
refresh on commit as
select 
  pk,
  roomnumber,
  count(*) over (partition by roomnumber) as cnt 
from roomservices;

alter table mv_roomservices add constraint 
  chk_max_2_services_per_room check (cnt <= 2);  

Now, whenever you add more than two services for a room and try to commit your transaction, you will get a ORA-12008 exception (error in materialized view refresh path).

---

Related Topics