We help IT Professionals succeed at work.

Triggers in Mysql

Kinderly Wade
on
358 Views
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.
Comment
Watch Question

Nem SchlechtIT Supervisor
CERTIFIED EXPERT
Top Expert 2009

Commented:
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.
Kinderly Wadeprogrammer

Author

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.
IT Supervisor
CERTIFIED EXPERT
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Kinderly Wadeprogrammer

Author

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.
Nem SchlechtIT Supervisor
CERTIFIED EXPERT
Top Expert 2009

Commented:
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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.