#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    6
    Rep Power
    0

    How to make a trigger for this sql?


    hi

    my database have two tables
    -table "invoice_detail" have colunms "invoice_id" and "item_totalafterdisc"
    -table "invoice" have colunms "invoice_id" and "invoice_total"

    I want to sum "item_totalafterdisc" in "invoice_detail" table for each invoice_id and put the sum value in "invoice" table "invoice_detail" colunm

    When i run this sql manually, it works.
    But when i put it in trigger, but it doesn't work=[

    Code:
     update invoice set 
    Invoice_total=(select sum(item_totalafterdisc) from invoice_detail 
    where invoice_detail.invoice_id = invoice.invoice_id);


    please help me~thanks
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    gee, i'm sorry, i am not familiar with the "it doesn't work" error message
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    6
    Rep Power
    0
    Originally Posted by r937
    gee, i'm sorry, i am not familiar with the "it doesn't work" error message
    i tried to put it on the trigger...
    when i update the "invoice_total" , the mysql cannot auto update and show...

    SQL error(1442):Can't update table 'invoice' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

    i don't know how to do now
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by tom525
    i don't know how to do now
    here's a suggestion... show us the trigger code
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    6
    Rep Power
    0
    Originally Posted by r937
    here's a suggestion... show us the trigger code
    actually i am already created two triggers, "before insert" and "before update" for the "invoice" table.

    this is the "before update" trigger
    Code:
    delimiter $
    CREATE TRIGGER invoice_bu BEFORE UPDATE ON invoice 
     FOR EACH ROW BEGIN 
     update invoice set
    Invoice_total=(select sum(item_totalafterdisc) from invoice_detail where invoice_detail.invoice_id = invoice.invoice_id);
      END $
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    okay, thanks

    that's seriously messed up, logically

    suppose you wanted to update an invoice to change the "date payment due" column -- why are you going to re-calculate the invoice amount? i think that's wrong

    the trigger(s) to update the invoice amount should be declared on the invoice_detail table, so that, for example, if another item is added, the total is re-calculated on the invoice
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    6
    Rep Power
    0
    Originally Posted by r937
    okay, thanks

    that's seriously messed up, logically

    suppose you wanted to update an invoice to change the "date payment due" column -- why are you going to re-calculate the invoice amount? i think that's wrong

    the trigger(s) to update the invoice amount should be declared on the invoice_detail table, so that, for example, if another item is added, the total is re-calculated on the invoice

    acutally the invoice_detail table is storing the product(s) that has been purchased by customers
    and it has these attributes

    invoice_id
    product_id
    product_name
    price
    qty
    disc
    item_totalafterdisc


    for example...if a customer purchase many products at the same time, then i need to sum up the "item_totalafterdisc" which are related to the invoice_id(eg INV000001) and auto send the value to invoice_total in the invoice table. is it impossible to do?thank you so much.
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by tom525
    is it impossible to do?
    if you try to do it with a trigger on the invoice table, yes

    try it on the invoice_detail table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    6
    Rep Power
    0
    Originally Posted by r937
    if you try to do it with a trigger on the invoice table, yes

    try it on the invoice_detail table
    ok i try first. thank you.
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    6
    Rep Power
    0
    Originally Posted by r937
    if you try to do it with a trigger on the invoice table, yes

    try it on the invoice_detail table
    problem solved....thank you so much!!!

IMN logo majestic logo threadwatch logo seochat tools logo