CODEWITHSUNDEEP
sqloracle-databasetriggers
Mor Goren
Mor Goren

Reputation: 105

The trigger compiled but fail while insert occurs, unintentional recursive

the Error report - ORA-00036: maximum number of recursive SQL levels (50) exceeded ORA-00036: maximum number of recursive SQL levels (50) exceeded ORA-06512: at "HW4.TR1", line 5 ORA-04088: error during execution of trigger 'HW4.TR1' ORA-06512: at "HW4.TR1", line 8 the code: 

 CREATE TABLE PIERS(
PID NUMBER PRIMARY KEY, 
NAME VARCHAR2(1),
CAPACITY NUMBER(5,0) NOT NULL,
TYPE VARCHAR2(20) UNIQUE,
TOTAL_SHIPS NUMBER DEFAULT 0 NOT NULL
);
CREATE TABLE SHIPS(
SID NUMBER PRIMARY KEY, 
NAME VARCHAR2(3),
COUNTRY VARCHAR(20),
CARGO_WIEGHT NUMBER(5,0) NOT NULL
);

CREATE TABLE DOCKING(
SID NUMBER,
PID NUMBER,
ARRIVAL_DATE DATE, 
DEPARTURE_DATE DATE,

CONSTRAINT FK_SID_DOCKING FOREIGN KEY (SID) REFERENCES SHIPS(SID),
CONSTRAINT FK_PID_DOCKING FOREIGN KEY (PID) REFERENCES PIERS(PID),
CONSTRAINT PK_DOCKING PRIMARY KEY (SID, PID, ARRIVAL_DATE, DEPARTURE_DATE)
);

INSERT INTO PIERS VALUES(1, 'A', 30000, 'agricultual exports',0);  
INSERT INTO PIERS VALUES(2, 'B', 30000, 'timber',0);
INSERT INTO PIERS VALUES(3, 'C', 30000, 'metals',0);
INSERT INTO PIERS VALUES(4, 'D', 30000, 'sling',0);
INSERT INTO PIERS VALUES(5, 'E', 60000, 'Panamax',0);
INSERT INTO PIERS VALUES(6, 'F', 30000, 'bulk',0);

INSERT INTO SHIPS VALUES(11, 'S1', 'China', 30000);  
INSERT INTO SHIPS VALUES(22, 'S2', 'Zimbabwe', 25000);
INSERT INTO SHIPS VALUES(33, 'S3', 'Guatemala', 15000);
INSERT INTO SHIPS VALUES(44, 'S4', 'China', 25000);
INSERT INTO SHIPS VALUES(55, 'S5', 'Marshall Islands', 20000);
INSERT INTO SHIPS VALUES(66, 'S6', 'Russia', 20000);
INSERT INTO SHIPS VALUES(77, 'S7', 'Malta', 45000);
INSERT INTO SHIPS VALUES(88, 'S8', 'Panama', 50000);
INSERT INTO SHIPS VALUES(99, 'S9', 'Malta', 15000);
INSERT INTO SHIPS VALUES(1010, 'S10', 'Marshall Islands', 20000);
INSERT INTO SHIPS VALUES(1111, 'S11', 'Liberia', 25000);
INSERT INTO SHIPS VALUES(1212, 'S12', 'Liberia', 15000);
INSERT INTO SHIPS VALUES(1313, 'S13', 'Zimbabwe', 20000);
INSERT INTO SHIPS VALUES(1414, 'S14', 'Panama', 55000);


CREATE OR REPLACE TRIGGER TR1 BEFORE INSERT OR UPDATE ON DOCKING 
FOR EACH ROW 
WHEN (TO_DATE(NEW.ARRIVAL_DATE) <= TO_DATE(NEW.DEPARTURE_DATE))
DECLARE 
cap PIERS.CAPACITY%TYPE; 
car SHIPS.CARGO_WIEGHT%TYPE; 
BEGIN 
SELECT CAPACITY INTO cap FROM PIERS WHERE PIERS.PID=:NEW.PID; 
SELECT CARGO_WIEGHT INTO car FROM SHIPS WHERE SHIPS.SID=:NEW.SID;
IF(car<= cap) THEN
    INSERT INTO DOCKING VALUES(:NEW.SID, :NEW.PID, TO_DATE(:NEW.ARRIVAL_DATE), TO_DATE(:NEW.DEPARTURE_DATE)); 
END IF;
EXCEPTION 
WHEN NO_DATA_FOUND THEN NULL; 
END; 
/

INSERT INTO DOCKING VALUES(88, 5, '15-AUG-17', '15-AUG-17');

Upvotes: 3

Views: 80

Answers (1)

Sanders the Softwarer
Sanders the Softwarer

Reputation: 2496

Your trigger says: "If somebody inserts a row into DOCKING I will insert yet another row into DOCKING". Thus trigger sees second insert and inserts third row.... thus sees third insert and inserts fourth...... And this music would be non-stop until ORA-00036...

Upvotes: 4

Related Questions