Results 1 to 12 of 12

Thread: update balance

  1. #1
    Join Date
    Jul 2006
    Posts
    6

    Question Unanswered: update balance

    I am trying to write a trigger which updates the customers balance when an invoice is deleted. The two tables (CUSTOMER, INVOICE) are created and I can post if needed, but I get a compilation error when I use the follow code.

    create or replace trigger trg_updatecustbalance2
    after delete on invoice
    for each row
    begin
    update customer
    set cust_balance = cust_balance - inv_amount
    where customer.cust_num = invoice.cust_num;
    end;
    /

    Can anyone help me figure out my error?
    Last edited by xxzimmer; 07-25-06 at 10:23.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Can anyone help me figure out where my error?
    Error? What error?
    I don't see any error.
    You're On Your Own (YOYO)!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2006
    Posts
    6
    Quote Originally Posted by anacedent
    >Can anyone help me figure out my error?
    Error? What error?
    I don't see any error.
    You're On Your Own (YOYO)!
    fix.t


    Now can we get back to my compilation error?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    What Anacedent was saying, in his/her usual charming way, was that you never said what error you are getting.

    However, I can see an error:
    Code:
    where customer.cust_num = invoice.cust_num;
    should be:
    Code:
    where customer.cust_num = :old.cust_num;

  5. #5
    Join Date
    Jul 2006
    Posts
    6
    Thanks for clearing that up for me and thanks for the help. I also added : old.inv_amount in there and it works great!

    I have one more question. I have to delete an invoice number when an invoice number is given as a parameter. I do not know how this one should be done at all. However, I have the following code and it too has compilation errors:


    create or replace procedure prc_inv_delete (w_in in number, w_cn in
    number, w_date in date, w_amt in integer)
    as
    begin
    delete from invoice
    set w_in = ld.inv_num
    values (w_in, w_cn, w_date, w_amt);

    end;
    /

    I ran it once and got errors so I added in more values, but I dont think I need them. Either way it does not work. Any help?

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    Are you trying to delete the record, or just update it. You have both syntax structures combined (actually INSERT syntax is in there too). To delete the entire record

    Code:
    create or replace procedure prc_inv_delete (w_in in number, w_cn in 
    number, w_date in date, w_amt in integer)
    as 
    begin
    
    delete from invoice
    where inv_num = w_in;
    
    end;
    /
    -cf

  7. #7
    Join Date
    Jul 2006
    Posts
    6
    Hmm...still is not working. With that code do I have to include all of the values, even if the table allows for null values?

    EDIT: Nevermind. I took out w_cn in number, w_date in date, and w_amt in integer and it works fine. Thanks guys!
    Last edited by xxzimmer; 07-25-06 at 11:46.

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Gald you got sorted. But please try to understand for future posts: you keep saying things like
    However, I have the following code and it too has compilation errors... I ran it once and got errors so I added in more values, but I dont think I need them. Either way it does not work. Any help?
    But then you don't tell us what the errors are! Why do you want us to guess all the time?

  9. #9
    Join Date
    Jul 2006
    Posts
    6

    Talking

    All it said was procedure/trigger created with compliation errors. It did not tell me more than that. From here on out I will try to do what I need it to do and if it gives me an error number or line I will post that. Sorry, but you are so good you did it without all of the information!

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    If you are using SQL Plus, just type this after the failed compilation:

    SHOW ERRORS

  11. #11
    Join Date
    Jul 2006
    Posts
    6
    Thanks for the tip!

  12. #12
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Just curious, why are you maintaining a balance amount. This (typically) is a very bad idea. If you need to get the current balance, simply issue a select

    select sum(amt)
    from invoice
    where cust_num = my_customer_num;

    You now have an up-to-date sum of all the invoices. Keeping a seperate balance is silly, it will always be prone to mistakes.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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