gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-11-30 21:34:23 25 4
gpt4 key购买 nike

我在 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"动态传递给触发器,以避免更新整个统计表?

最佳答案

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

话虽如此,一个trigger可以使用 NEW.athlete_nameOLD.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.)

关于MySQL:如何将参数传递给触发器,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/54727674/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com