问题:
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;