'Oracle 21c PRAGMA autonomous_transaction causes infinite execution
Exectuion of provided code causes infinite execution.
CREATE TRIGGER UnosRokPlacanja
BEFORE INSERT ON StavkaFaktureKD
REFERENCING NEW AS NEW
FOR EACH ROW
DECLARE
rok_placanja_temp DATE;
pragma autonomous_transaction;
BEGIN
SELECT rok_placanja
INTO rok_placanja_temp
FROM FakturaKupac
WHERE broj_fakture_kupac = :new.broj_fakture_kupac;
EXECUTE IMMEDIATE 'ALTER TRIGGER ZABRANAPROMENEROKAPLACANJA DISABLE';
:new.rok_placanja := rok_placanja_temp;
EXECUTE IMMEDIATE 'ALTER TRIGGER ZABRANAPROMENEROKAPLACANJA ENABLE';
COMMIT;
END;
Purpose of the trigger is to modify property rok_placanja during insertion in StavkaFaktureKD table. This property cannot be modifyed directly using UPDATE statement, because there is another trigger which prevents this (because of business logic). When new record is inserted in StavkaFaktureKD, before inserting, trigger UnosRokPlacanja is activated and it retrieves rok_placanja from referenced record in FakturaKupac table. Retrieved rok_placanja will replace rok_placanja of the new record.
I'm going to provide some more scripts to make problem reproducible.
CREATE TABLE FakturaKupac (
broj_fakture_kupac INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
kupac_PIB INT not null,
broj_ponude INT not null,
broj_racuna NUMBER,
rok_placanja DATE,
datum_dokumenta DATE,
ukupna_cena NUMBER,
CONSTRAINT faktura_kupac_kupac
FOREIGN KEY(kupac_PIB, broj_ponude)
REFERENCES PonudaKupac(kupac_PIB, broj_ponude)
);
CREATE TABLE StavkaFaktureKD (
broj_stavke INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
broj_fakture_kupac INT not null,
kolicina NUMBER,
jedinicna_cena NUMBER,
rok_placanja DATE,
CONSTRAINT broj_fakture_kd_stavka_fakture
FOREIGN KEY(broj_fakture_kupac)
REFERENCES FakturaKupac(broj_fakture_kupac)
);
CREATE OR REPLACE TRIGGER ZabranaPromeneRokaPlacanja
BEFORE UPDATE OF rok_placanja ON StavkaFaktureKD
BEGIN
RAISE_APPLICATION_ERROR(-20001,'Nije moguca izmena vrednosti roka placanja!');
END;
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
