MySQL: How to pass parameter to a trigger

问题: I have a table on a mysql 5.7 db, containing say athletes with their mean, max, avg times in a specific sport. I have another table that lists some calculated statistics ba...

问题:

I have a table on a mysql 5.7 db, containing say athletes with their mean, max, avg times in a specific sport. I have another table that lists some calculated statistics based on those values.

I managed to do the calculcations that end up on the second using stored procedures. I use as input parameter to the stored procedure the athlete's name.

So when in the first table, an athlete is inserted (with his/her avg/min/max times) or his/her values are updated and I run the stored procedure, the later updates the statistics table.

My question is how to achieve the same result with triggers?

I guess it is feasible/easy to update the entire table on each insert or update of the first table. What would be more efficient performance-wise, would be on each :

INSERT into table1 values (..) where athlete_name="John Do"

(...)

ON DUPLICATE KEY UPDATE (...)

Run a trigger in the pseudocode form :

INSERT into statistics_table values (..) where athlete_name="John Do"

ON DUPLICATE KEY UPDATE (...)

How can the the athlete_name="John Do" be passed to the trigger dynamically, to avoid update the entire statistics table?


回答1:

You cannot pass any parameters to a trigger and the insert statement does not support the where clause either.

Having said this, a trigger can pick up the user's name from the record being inserted / updated / deleted using NEW.athlete_name or OLD.athlete_name (whichever is required) and use that to call a stored procedure:

Within the trigger body, the OLD and NEW keywords enable you to access columns in the rows affected by a trigger. OLD and NEW are MySQL extensions to triggers; they are not case-sensitive.

In an INSERT trigger, only NEW.col_name can be used; there is no old row. In a DELETE trigger, only OLD.col_name can be used; there is no new row. In an UPDATE trigger, you can use OLD.col_name to refer to the columns of a row before it is updated and NEW.col_name to refer to the columns of the row after it is updated.

A column named with OLD is read only. You can refer to it (if you have the SELECT privilege), but not modify it. You can refer to a column named with NEW if you have the SELECT privilege for it. In a BEFORE trigger, you can also change its value with SET NEW.col_name = value if you have the UPDATE privilege for it. This means you can use a trigger to modify the values to be inserted into a new row or used to update a row. (Such a SET statement has no effect in an AFTER trigger because the row change will have already occurred.)


回答2:

You can create triggers that fire after each insert or update on the parent table (athletes). Within each trigger, you can access the value of column athlete_name on the record that was just created or changed, and then invoke your stored procedure using CALL().

Here is a code sample for such an INSERT trigger :

CREATE TRIGGER athletes_upd AFTER INSERT ON athletes 
FOR EACH ROW
BEGIN
    CALL my_procedure(NEW.athlete_name);
END;

UPDATE trigger :

CREATE TRIGGER athletes_upd AFTER UPDATE ON athletes 
FOR EACH ROW
BEGIN
    CALL my_procedure(NEW.athlete_name);  -- or maybe OLD.athlete_name ?
END;
  • 发表于 2019-02-21 07:26
  • 阅读 ( 240 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除