Trigger IF Inserting Loop /Oracle

Question

I am trying to Insert new row in Table StavkaUgovora (ContractItem) and trigger/procedure should calculate (+/-) jedinicna_cena (price) and kolicina (amount) into the ukupna_cena (total_price) at table Ugovor(contract). I already have update and delete and that works fine, but for inserting statement script go into the loop and multiplicate data without stopping. Where i wrong?

CREATE TABLE StavkaUgovora (
broj_stavke INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
broj_ugovora INT not null, 
kolicina NUMBER, 
tip_usluge VARCHAR2(255), 
jedinicna_cena NUMBER,
CONSTRAINT stavka_ugovora_broj_ugovora
    FOREIGN KEY(broj_ugovora)
    REFERENCES Ugovor(broj_ugovora));

CREATE TABLE Ugovor (
broj_ugovora INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
broj_ponude INT not null, 
datum_sklapanja DATE,
ukupna_cena NUMBER,
CONSTRAINT broj_ponude_ugovor
    FOREIGN KEY(broj_ponude)
    REFERENCES Ponuda(broj_ponude)
);

CREATE OR REPLACE PROCEDURE Zbir (brojUgovoraIN NUMBER, cenaSaberiIN NUMBER, cenaOduzmiIN 
NUMBER)
AS suma UGOVOR.broj_ugovora%type;
pragma autonomous_transaction;
BEGIN suma:=0;
EXECUTE IMMEDIATE 'ALTER TRIGGER ZABRANAIZMENEUKUPNECENE DISABLE';
SELECT ukupna_cena INTO suma
FROM UGOVOR
WHERE broj_ugovora=brojUgovoraIN; 
UPDATE UGOVOR
SET ukupna_cena=suma + cenaSaberiIN - cenaOduzmiIN
WHERE broj_ugovora=brojUgovoraIN;
EXECUTE IMMEDIATE 'ALTER TRIGGER ZABRANAIZMENEUKUPNECENE ENABLE';
COMMIT;
END;


CREATE OR REPLACE TRIGGER "SIFRAUGOVORA"
BEFORE INSERT OR UPDATE OR DELETE ON STAVKAUGOVORA
FOR EACH ROW
DECLARE 
brojUgovora NUMBER;
cenaSaberi NUMBER;
cenaOduzmi NUMBER;
BEGIN
    IF INSERTING
    THEN
        BEGIN 
        brojUgovora := :NEW.broj_ugovora;
        cenaSaberi  := :NEW.jedinicna_cena * :NEW.kolicina;
        cenaOduzmi  := 0;
        END;
    ELSIF UPDATING
    THEN
        BEGIN 
        brojUgovora := :NEW.broj_ugovora;
        cenaSaberi  := :NEW.jedinicna_cena * :NEW.kolicina;
        cenaOduzmi  := :OLD.jedinicna_cena * :OLD.kolicina;
        END;
    ELSE
        BEGIN 
        brojUgovora := :OLD.broj_ugovora;
        cenaSaberi  := 0;
        cenaOduzmi  := :OLD.jedinicna_cena * :OLD.kolicina;
        END;
    END IF;
    BEGIN
        Zbir(brojUgovora, cenaSaberi, cenaOduzmi);
    END;
END;

Littlefoot · Accepted Answer · 2022-01-26 07:23:58Z

It works for me (after some modifications as you didn't post all tables/triggers involved).

Tables:

SQL> CREATE TABLE ugovor
  2  (
  3     broj_ugovora      INT PRIMARY KEY,
  4     broj_ponude       INT NOT NULL,
  5     datum_sklapanja   DATE,
  6     ukupna_cena       NUMBER
  7  );

Table created.

SQL> CREATE TABLE stavkaugovora
  2  (
  3     broj_stavke      INT PRIMARY KEY,
  4     broj_ugovora     INT NOT NULL,
  5     kolicina         NUMBER,
  6     tip_usluge       VARCHAR2 (255),
  7     jedinicna_cena   NUMBER,
  8     CONSTRAINT stavka_ugovora_broj_ugovora FOREIGN KEY (broj_ugovora)
  9        REFERENCES ugovor (broj_ugovora)
 10  );

Table created.

Procedure:

SQL> CREATE OR REPLACE PROCEDURE zbir (brojugovorain  NUMBER,
  2                                    cenasaberiin   NUMBER,
  3                                    cenaoduzmiin   NUMBER)
  4  AS
  5     suma  ugovor.broj_ugovora%TYPE;
  6     PRAGMA AUTONOMOUS_TRANSACTION;
  7  BEGIN
  8     suma := 0;
  9
 10     --EXECUTE IMMEDIATE 'ALTER TRIGGER ZABRANAIZMENEUKUPNECENE DISABLE';
 11     SELECT ukupna_cena
 12       INTO suma
 13       FROM ugovor
 14      WHERE broj_ugovora = brojugovorain;
 15
 16     UPDATE ugovor
 17        SET ukupna_cena = suma + cenasaberiin - cenaoduzmiin
 18      WHERE broj_ugovora = brojugovorain;
 19
 20     --EXECUTE IMMEDIATE 'ALTER TRIGGER ZABRANAIZMENEUKUPNECENE ENABLE';
 21     COMMIT;
 22  END;
 23  /

Procedure created.

Trigger:

SQL> CREATE OR REPLACE TRIGGER sifraugovora
  2     BEFORE INSERT OR UPDATE OR DELETE
  3     ON stavkaugovora
  4     FOR EACH ROW
  5  DECLARE
  6     brojugovora  NUMBER;
  7     cenasaberi   NUMBER;
  8     cenaoduzmi   NUMBER;
  9  BEGIN
 10     IF INSERTING
 11     THEN
 12        brojugovora := :new.broj_ugovora;
 13        cenasaberi := :new.jedinicna_cena * :new.kolicina;
 14        cenaoduzmi := 0;
 15     ELSIF UPDATING
 16     THEN
 17        brojugovora := :new.broj_ugovora;
 18        cenasaberi := :new.jedinicna_cena * :new.kolicina;
 19        cenaoduzmi := :old.jedinicna_cena * :old.kolicina;
 20     ELSE
 21        brojugovora := :old.broj_ugovora;
 22        cenasaberi := 0;
 23        cenaoduzmi := :old.jedinicna_cena * :old.kolicina;
 24     END IF;
 25
 26     DBMS_OUTPUT.put_line ('ugovor: ' || brojugovora);
 27     zbir (brojugovora, cenasaberi, cenaoduzmi);
 28  END;
 29  /

Trigger created.

Testing: insert UGOVOR first and then COMMIT because the zbir procedure is declared as an autonomous transaction so - if you don't COMMIT - it won't see newly added rows into UGOVOR table.

SQL> SET SERVEROUTPUT ON
SQL>
SQL> INSERT INTO ugovor (broj_ugovora,
  2                      broj_ponude,
  3                      datum_sklapanja,
  4                      ukupna_cena)
  5       VALUES (1,
  6               1,
  7               SYSDATE,
  8               100);

1 row created.

SQL> COMMIT;

Commit complete.

Insert stavkaugovora:

SQL> INSERT INTO stavkaugovora (broj_stavke,
  2                             broj_ugovora,
  3                             kolicina,
  4                             tip_usluge,
  5                             jedinicna_cena)
  6       VALUES (1,
  7               1,
  8               10,
  9               'A',
 10               5);
ugovor: 1

1 row created.

No errors, no "loop". The result:

SQL> SELECT * FROM ugovor;

BROJ_UGOVORA BROJ_PONUDE DATUM_SKLAPANJA     UKUPNA_CENA
------------ ----------- ------------------- -----------
           1           1 26.01.2022 08:21:21         150

SQL> SELECT * FROM stavkaugovora;

BROJ_STAVKE BROJ_UGOVORA   KOLICINA TIP_USLUGE JEDINICNA_CENA
----------- ------------ ---------- ---------- --------------
          1            1         10 A                       5

SQL>

Nikola Stanojevic · Accepted Answer · 2022-01-29 13:58:58Z

0

Thanks for answer! Everything works fine when put in comment autonomous transaction and manualy disable trigger. I use autonomous transaction to disable trigger ZabranaIzmeneUkupneCene which prohibits direct input values into ukupna_cena on table UGOVOR. I saw in your code autonomous transaction is in comment too.

Do you have advice how to disable trigger 'ZabranaIzmeneUkupneCene' while procedure 'Zbir' is active?

CREATE OR REPLACE TRIGGER ZabranaIzmeneUkupneCene
BEFORE UPDATE OF ukupna_cena ON UGOVOR
FOR EACH ROW
BEGIN
RAISE_APPLICATION_ERROR (-20001, 'Nije moguca promena ukupne cene direktno!');
END;

edited Jan 29, 2022 at 13:58

answered Jan 26, 2022 at 16:45

Nikola Stanojevic

334 bronze badges

2 Comments

Ana GH Over a year ago

I don't think an autonomous transaction works for enabling/disabling triggers, an autonomous transaction is used to make commits and rollbacks into the database independent of the main code of your procedure. For example, if you have tables auditing operations in your database, and you want to record an error into the auditing table before doing rollback, you use an autonomous transaction to commit the insert into the audit table before doing a rollback of the main transaction.

Nikola Stanojevic Over a year ago

OK, thanks for explaining autonomous transaction. Do you have advice how to disable trigger 'ZabranaIzmeneUkupneCene' while procedure 'Zbir' is active?

Collectives™ on Stack Overflow

Trigger IF Inserting Loop /Oracle

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related