I have a problem with a trigger.

The function of the trigger is to check if new items already exists within the database. The problem is that the query returns an error stating that

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


Basically my problem is first I have to identify the newest record and store the title (new_title) (the attribute I will use to compare)

Select MAX(itemno), title into num, new_title from item;

Then I need to take the stored attribute, and use it in another query.
To find a data which correponds to the new variable

select title into old_title from item where title=(new_title) and itemno<>num;

This query looks for titles with the same name but not the same itemno. (so that it doesnt return the new entry)

An If statement is then used to decide the action, when the same entry has been found.

The problem is PL/SQL doesnt seem to allow it.

Any ideas??
Thanks for any help..

First of all, this query cannot work :
Select MAX(itemno), title into num, new_title from item;
=> ORA-00937: not a single-group group function

Rewrite it as :
select itemno, title into num, new_title from item where itemno = (select max(itemno) from items)




About the error message : ORA-01422: exact fetch returns more than requested number of rows. It means exactly what it means. A "SELECT INTO" query can only return 0 or 1 row, not more. Declare your select as a cursor, and process on a row-by-row basis.

Good luck.

(WOW) I still have a lot to learn.

Thank you cvandemaele