Oracle row trigger to compound trigger-CodePudding

I have a problem with making compound trigger from my row trigger or if someone have better solution than compound trigger I want to hear. Idea is when someone update column 'kupac_pib' in 'KONTAKT' table fire trigger which will change 'naziv_firme_kupac' in 'KONTAKT' table, but naziv_firme_kupac is from 'KUPAC' table.

Error is ORA-04091: table C##NIKOLA.KONTAKT is mutating, trigger/function may not see it

CREATE TABLE Kupac (
    kupac_PIB INT PRIMARY KEY,
    naziv_firme_kupac VARCHAR2(255)
);

CREATE TABLE Kontakt (
    id_kontakt INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    mail VARCHAR2(100),  
    telefon VARCHAR2(100),
    kupac_PIB INT, 
    partner_PIB INT, 
    naziv_firme_kupac VARCHAR2(255),
    naziv_poslovni_partner VARCHAR2(255),
    CONSTRAINT kontakt_kupac
        FOREIGN KEY(kupac_PIB)
        REFERENCES Kupac(kupac_PIB),
    CONSTRAINT kontakt_pPartner
        FOREIGN KEY(partner_PIB)
        REFERENCES PPartner(partner_PIB)
);

CREATE OR REPLACE TRIGGER IzmenaNazivaKupcaNaOsnovuPIB_a
AFTER UPDATE OF kupac_PIB ON Kontakt
FOR EACH ROW
DECLARE
noviNazivKupca VARCHAR2(255);
kupac_PIB_tmp INT;
    BEGIN
        noviNazivKupca:= :new.naziv_firme_kupac;
        kupac_PIB_tmp := :new.kupac_PIB;
        UPDATE Kontakt
        SET naziv_firme_kupac = noviNazivKupca
        WHERE kupac_PIB_tmp = (SELECT kupac_PIB FROM Kupac
        WHERE kupac_PIB_tmp = kupac_PIB);
    END;

CodePudding user response：

You need to update your trigger to -

CREATE OR REPLACE TRIGGER IzmenaNazivaKupcaNaOsnovuPIB_a
BEFORE UPDATE OF kupac_PIB ON Kontakt
FOR EACH ROW
DECLARE
kupac_PIB_tmp VARCHAR2(255);
BEGIN
        SELECT naziv_firme_kupac
          INTO kupac_PIB_tmp
          FROM Kupac
         WHERE kupac_PIB = :new.kupac_PIB;
         
        :new.naziv_firme_kupac := kupac_PIB_tmp;
END;
/

Demo.