Triggers in Mysql

Posted on 2014-08-06
Last Modified: 2014-08-07
Dear Experts,

I've being working on triggers in mysql lately. I come across this issue that when I want to pass a parameter into the trigger, I cannot find a way to do so. I've read the Mysql some online post that I am not allowed to pass parameters into triggers like store procedures or function.

I wish to know if I still can pass parameters into trigger by other methods beside inserts the value into database first and then uses keyword 'new' or 'old' to get the job done in conjunction with DELETE, UPDATE, and INSERT commands? Thanks.
Question by:Kinderly Wade
    LVL 23

    Expert Comment

    What value do you want passed to the trigger?

    The only way would be to somehow update a table, such as "trigger_parameters" and put some data in there and then have the trigger read from this table.  However, the data you try to INSERT/SELECT from with the table will be unreliable, as the trigger may be called twice in quick succession (ie, the value written to this table will be over-written by the second value before the 1st one is read by the trigger).

    If you could give a more concrete explanation of what you're trying to do, we all might be able to provide a better solution.

    Author Comment

    by:Kinderly Wade
    Hi newmws1,

    I have two tables:
    1. order_header
    2. order_detail

    Within my order_detail, I have a FK of order_header's id (ordRefHd_id). This way I can refer the number rows from order_detail to a single id (id) in order_header. Example will be like this:

    id      order#       ref_id    company
    1.       0001           S212        GWK01
    2.       0002           S331        OPT22


    id    item          cost         qty     ordHdRef_id
    1     331223       33.21        1                  1
    2     232112        54.23       3                  1

    I wish to auto update the ordHdRef_id with trigger with before insert that when a new order header is being created in order_header table, I will be able to update ordHdRef_id in the order_detail with the correct order header id reference.

    LVL 23

    Accepted Solution

    Gotcha - and you want the trigger on the order_detail table so it'll fill in ordHdRef_Id automatically.

    Usually, I would insert into the order_header table first, grab the new order ID using LAST_INSERT_ID() and then use that when inserting orders into the order_detail table (and skip the trigger altogether).

    How do you know if a new entry to order_detail requires a new order header or if its part of existing order?

    Author Comment

    by:Kinderly Wade
    there will only be one open order at a time. I will have a marker for the submitted orders as in 'S'. Thanks for the feedback. I will use the LAST_INSERT_ID(). Thanks.

    I was trying to make the trigger to call the function but still I need to pass the order header id into the function but the trigger will not take the parameter.
    LVL 23

    Expert Comment

    It's difficult to guarantee only one open order at a time unless you are the only one using your application.  If you are *positive* this is the case (and I hate even giving you this example, because its wrong on many levels), you can just do this in your trigger:
    DECLARE last_order_id INT;
    SELECT MAX(id) INTO last_order_id FROM order_header;

    Open in new window


    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
    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…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.

    729 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

    20 Experts available now in Live!

    Get 1:1 Help Now