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

    Unhappy Unanswered: Please help with a decrement function

    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;


    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;


    button_when_pressed trigger is


  2. #2
    Join Date
    Feb 2002
    Houston, TX
    how about something like this?

    PROCEDURE Decrement(@dvd_id integer) 
    begin transaction
    if exists(select * From dvd where dvd_id = @dvd_id) begin
      if exists(select * From dvd where dvd_id = @dvd_id and copies >= 1) begin
        update dvd set copies = copies - 1 where dvd_id = @dvd_id
      end else begin
        raiserror('No available copies of DVD (%d) were found!.',16,1,@dvd_id)
        return -1
    end else begin
      raiserror('Referenced DVD (%d) not found.',16,1,@dvd_id)
      return -1
    commit transaction
    return 0
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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