We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Setting a Trigger to Calculate an Invoice Total

jimbo2k2
jimbo2k2 asked
on
Medium Priority
695 Views
Last Modified: 2012-05-06
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)
description
line_total

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.

Regards,
James

Comment
Watch Question

Commented:
After Insert Trigger for line_items :
Update purchase p set grand_total = grand_total + new.line_total
Where p.purchase_id = new.purchase_id

After Update Trigger for line_items :
Update purchase p set grand_total = grand_total + new.line_total - old.line_total
Where p.purchase_id = new.purchase_id

After Delete Trigger for line_items :
Update purchase p set grand_total = grand_total - old.line_total
Where p.purchase_id = old.purchase_id

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Dear TKorhan,

Thank you very much. I have tried the Insert trigger and it works perfectly, I will proceed with the others now.

I do have another question though. What purpose does the 'p' serve? I have starred the bit I mean for future readers.


CREATE TRIGGER mytrigger
AFTER INSERT ON line_items
FOR EACH ROW
UPDATE purchase **p**
SET grand_total = grand_total + new.line_total
WHERE **p**.purchase_id = new.purchase_id


I have a feeling this is the key to making the trigger work? Why do you link the 'p' to the purchase table, but use it in a WHERE clause which references a field in the line_items table?

Thanks again, you have rescued my afternoon! I'll mark the above as a solution when I have tested the other two triggers.

Author

Commented:
The only information 'missing' was the actual CREATE TRIGGER syntax, easily found on the mySQL website.

The syntax posted is 100% accurate and can be copied straight into your query  after FOR EACH ROW.

A great help, thank you.


Commented:
It is only giving short name for easiest way when using long queries. (you can use **p**,**prc**,**pr1** exc.). It is my addiction but if you do not want to use it, your sql must be like this :

CREATE TRIGGER mytrigger
AFTER INSERT ON line_items
FOR EACH ROW
UPDATE purchase
SET grand_total = grand_total + new.line_total
WHERE purchase.purchase_id = new.purchase_id

Author

Commented:
Thanks again, fully understood.

A great help!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.