MySQL:如何将参数传递给触发器

Question

我在 mysql 5.7 上有一张 table db，包含说 athletes 以及他们在特定运动中的平均、最大、平均时间。我有另一个表格，列出了一些基于这些值的计算统计数据。

我设法使用存储过程进行了第二次计算。我使用运动员姓名作为存储过程的输入参数。

因此，当在第一个表中插入运动员(包括他/她的平均/最小/最大时间)或更新他/她的值时，我运行存储过程，稍后更新统计信息 表。

我的问题是如何使用触发器获得同样的结果？

我想在第一个表的每次插入或更新时更新整个表是可行/容易的。在性能方面更有效的是，每个 :

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

(...)

ON DUPLICATE KEY UPDATE (...)

以伪代码形式运行触发器:

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

ON DUPLICATE KEY UPDATE (...)

如何才能athlete_name="John Do"动态传递给触发器，以避免更新整个统计表？

Answer 1

您不能将任何参数传递给触发器，insert 语句也不支持 where 子句。

话虽如此，一个trigger可以使用 NEW.athlete_name 或 OLD.athlete_name(以需要的为准)从正在插入/更新/删除的记录中获取用户名，并使用它来调用存储过程:

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.)