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;
CodePudding user response：
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>
CodePudding user response：
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.
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;