Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002
    Posts
    2

    Unhappy Unanswered: please help with decrement procedure

    Hi, I'm trying to leard DB and this procedure has been giving me greef all day.

    I basically made a small database to simulate video rental, very simple... What I am trying to do here is have a button, when pressed, will decrement the copies counter in the dvd table by 1.

    So if there is a dvd with 10 copies and you rent one out, the copies should become 9...

    Problem I get here is the 'too many rows' error... Is there any way to do this differently or fix this procedure? Any help is appreciated thanks.


    PROCEDURE Decrement(dvd_id number) IS


    copy number;

    BEGIN

    select copies
    into copy
    from DVD
    where dvd_id = dvd.dvd_id;

    copy := copy -1;

    update dvd
    set copies = copy
    where dvd_id = dvd.dvd_id;

    END;

    button_when_pressed trigger is

    decrement(:rental_line.dvd_id);

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: please help with decrement procedure

    If you are getting TOO_MANY_ROWS then that means that dvd_id is not a primary or unique key for table DVD, which sounds like a cause for concern!

    That aside, you can do this all in one step anyway:

    PROCEDURE Decrement(dvd_id number) IS
    BEGIN
    update dvd
    set copies = copies - 1
    where dvd_id = dvd.dvd_id;
    END;

    (But this will update more than one record if the dvd_id value is not unique).

Posting Permissions

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