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

on
Medium Priority
695 Views
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

## View Solution Only

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.

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.

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

Commented:
Thanks again, fully understood.

A great help!
##### Thanks for using Experts Exchange.

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