Results 1 to 3 of 3

Thread: Trigger Problem

  1. #1
    Join Date
    Sep 2003
    Posts
    12

    Unanswered: Trigger Problem

    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..

  2. #2
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    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.

  3. #3
    Join Date
    Sep 2003
    Posts
    12
    (WOW) I still have a lot to learn.

    Thank you cvandemaele

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •