Setting a Trigger to Calculate an Invoice Total
Posted on 2009-02-16
Hi folks, first post on here so hope you can help.
I am developing a web-based administration system for my business using PHP and mySQL. I need some assistance in getting my trigger syntax correct.
The following example is for logging a new purchase. We create a purchase, assigning it a unique ID (I have left out other details like date, customer reference etc).
The table line_items references the purchase_id, and is used to store the contents of the purchase. Each line item has a line_total.
Tables as follows:
--- purchase ---
purchase_id (INT, PK)
grand_total (DECIMAL 10,2)
--- line_items ---
line_id (INT, PK)
purchase_id (INT, FK)
My trigger needs to say "OK, you have inserted or updated something in the line_items table. I am now going to see which purchase_id was added/updated, then add up all the line_totals with that purchase_id. I will finally apply this amount to the relevant grand_total field in the purchases table."
As an example...I might have a purchase_id of 631. It contains apples ($5) and oranges ($10) in the line_items table, so grand_total should read $15. I add bananas ($10) to line_items, and having added the bananas, the trigger should automatically update my grand_total to $25.
Hope this makes sense. Thanks for your time and I hope you can help.