CODEWITHSUNDEEP
oracle-databaseora-01422
Yilin
Yilin

Reputation: 41

ORA-01422: exact fetch returns more than requested number of rows for trigger

I tried to insert new rows to check if my trigger works. But it returns the error in the title

Upvotes: 1

Views: 110

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50037

One big problem you have is that your joins are written improperly. I don't know what you're trying to do, but the part of your query which reads

INNER JOIN PURCHASE
  ON :NEW.SERVEDBY = P.EMPNO

will join every row from the PURCHASE table to the P subquery if :NEW.SERVEDBY happens to equal the value of EMPNO in any row returned by the P subquery. It may actually join every row from PURCHASE multiple times, if the conditions :NEW.SERVEDBY = P.EMPNO is matched multiple times. I suspect this isn't what you wanted, but since I don't know what your tables look like or what it is you're attempting to accomplish I can't suggest an approach to take.

Upvotes: 1

Popeye
Popeye

Reputation: 35920

Following query is returning more than one record.

SELECT DNAME INTO DeptName FROM (SELECT * FROM DEPT INNER JOIN EMP ON 
 DEPT.DEPTNO = EMP.DEPTNO) P INNER JOIN PURCHASE ON :NEW.SERVEDBY = 
 P.EMPNO; 
-- use fetch first 1 row only or row_number analytical function or rownum pseudo column

You need to limit it to 1 record as INTO can take only one record.

Cheers!!

Upvotes: 0

Related Questions