Mutating table error when trying to Insert into Table with select

Question

I have a problem having mutating table error when inserting data from select in PLSQL.

When im doing insert like this:

insert into pozycje_dokumentow
(
        pozycja_dokumentu,
            id_dok,
            stawka_vat,
             miejsce_skladowania,
             jm_kod_jednostka_miary,
             ilosc_jm_sprzedazy,
             ilosc,
             indeks_czesci
)
values(
        1014,
        1882706,
        23,
        4709,
        'L15',
        388.33,
        386.713,
        26539
);

It all works good, but when i try to do it in this way:

insert into pozycje_dokumentow
(
        pozycja_dokumentu,
            id_dok,
            stawka_vat,
             miejsce_skladowania,
             jm_kod_jednostka_miary,
             ilosc_jm_sprzedazy,
             ilosc,
             indeks_czesci
)
Select
        1014,
        1882706,
        23,
        4709,
        'L4',
        388.33,
        386.713,
        26539
from dual

I get an error in before_insert trigger on table pozycje_dokumentow: ORA-20298: ORA-04091: ORA-04091: table name is mutating, trigger/function may not see it

What is the diffrence in those two querys?

Trigger's body that is generating error, only when doing insert select from the same table that trigger is created:

select nvl(max(lp),0) + 1
    into :new.lp
    from pozycje_dokumentow
   where id_dok = :new.id_dok
   group by id_dok;

Answer 1

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;