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.
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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;
....
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.