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?