Setting a Trigger to Calculate an Invoice Total

Posted on 2009-02-16
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)

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.


Question by:jimbo2k2
    LVL 4

    Accepted Solution

    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

    Author Comment

    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
    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 Closing Comment

    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.

    LVL 4

    Expert Comment

    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
    UPDATE purchase
    SET grand_total = grand_total + new.line_total
    WHERE purchase.purchase_id = new.purchase_id

    Author Comment

    Thanks again, fully understood.

    A great help!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (, A SQLite Tidbit: Quick Numbers Table Generation (…
    I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now