Search code examples
sqloracle-databaseora-01422

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

I have tired to create a trigger like this, but it shows this error

Solution

  • This error indicates that your query SELECT DNAME INTO DeptName ... returns more than one record, hence its result cannot be assigned to a scalar variable. The problem with your query is that you have an unecessary JOIN on purchase, so it returns one record for each sale performed by the employee that just did the current sale.

    I believe that your query can be simplified as follows:

    SELECT d.dname INTO DeptName
FROM emp e
INNER JOIN dept d ON e.deptno = d.deptno
WHERE e.empno = :NEW.servedby;