sql - 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,
        'L15',
        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 dual:

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

标签： sqloracleplsql

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;