Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Setting a Trigger to Calculate an Invoice Total

Posted on 2009-02-16
5
Medium Priority
?
657 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

0
Comment
Question by:jimbo2k2
  • 3
  • 2
5 Comments
 
LVL 4

Accepted Solution

by:
TKorhan earned 2000 total points
ID: 23650465
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
0
 

Author Comment

by:jimbo2k2
ID: 23650908
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.
0
 

Author Closing Comment

by:jimbo2k2
ID: 31547318
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.


0
 
LVL 4

Expert Comment

by:TKorhan
ID: 23656811
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
0
 

Author Comment

by:jimbo2k2
ID: 23657557
Thanks again, fully understood.

A great help!
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question