Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 343
  • Last Modified:

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.
0
Kinderly Wade
Asked:
Kinderly Wade
  • 3
  • 2
1 Solution
 
nemws1Commented:
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 WadeAuthor 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
 
nemws1Commented:
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
 
Kinderly WadeAuthor 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
 
nemws1Commented:
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now