sql - 触发器已编译但在插入发生时失败，无意递归

Question

错误报告 -ORA-00036: 超出递归 SQL 级别的最大数目 (50)ORA-00036: 超出递归 SQL 级别的最大数目 (50)ORA-06512: 在“HW4.TR1”，第 5 行ORA-04088: 执行触发器 'HW4.TR1' 时出错ORA-06512: 在“HW4.TR1”，第 8 行代码:

 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');

Answer 1

你的触发器说:“如果有人在 DOCKING 中插入一行，我将在 DOCKING 中插入另一行”。因此触发器看到第二个插入并插入第三行......因此看到第三个插入并插入第四个......并且这个音乐将不间断直到 ORA-00036......