Triggers in Mysql

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.
Kinderly WadeprogrammerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nemws1Database AdministratorCommented:
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.
0
Kinderly WadeprogrammerAuthor Commented:
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:

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

order_detail:

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.

Thanks.
0
nemws1Database AdministratorCommented:
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?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kinderly WadeprogrammerAuthor Commented:
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.
0
nemws1Database AdministratorCommented:
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

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.