Results 1 to 3 of 3

Thread: pl/sql problem

  1. #1
    Join Date
    Sep 2003
    Posts
    6

    Unanswered: pl/sql problem

    hi i always get the raise application error even though the new.balance- new.trans_amount is greater than the p.overdraft.

    here is my code:
    create or replace trigger withpur
    after insert on mbtrans
    for each row
    declare p mbaccount%rowtype;
    begin
    select * into p from mbaccount where :new.account_no=account_no;
    if (:new.balance-:new.trans_amount)< p.overdraft and :new.trans_type='WITH' or :new.trans_type='PUR' then
    raise_application_error(-20112, 'balance below credit limit');
    else
    update mbaccount
    set balance = balance - (:new.trans_amount)
    where account_no= :new.account_no and :new.trans_type='WITH' or :new.trans_type='PUR';
    end if;
    end;

    when i insert the row its meant to update yet i get the application error.
    any hints of what i have done wrong
    thanks

  2. #2
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Hi,

    Don't know much about development so if I'm wrong don't kill me but if the

    (:new.balance-:new.trans_amount)< p.overdraft

    is always FALSE

    then the other bit

    and :new.trans_type='WITH' or :new.trans_type='PUR'

    must be making it TRUE to raise the error.

    Like I say, I ain't sure.

    Rgs,
    Breen.

  3. #3
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Try

    if (:new.balance-:new.trans_amount)< p.overdraft and (:new.trans_type='WITH' or :new.trans_type='PUR') then
    with "(" and ")"

    HTH
    Gregg

Posting Permissions

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