在触发器中更新后更新同一行

2022-01-01 00:00:00 sql mysql triggers

我希望 epc 列始终为 earnings/clicks.我正在使用 AFTER UPDATE 触发器来完成此操作.因此，如果我要向该表添加 100 次点击，我希望 EPC 自动更新.

I want the epc column to always be earnings/clicks. I am using an AFTER UPDATE trigger to accomplish this. So if I were to add 100 clicks to this table, I would want the EPC to update automatically.

我正在尝试:

CREATE TRIGGER `records_integrity` AFTER UPDATE ON `records` FOR EACH ROW SET 
NEW.epc=IFNULL(earnings/clicks,0);

并收到此错误:

MySQL said: #1362 - Updating of NEW row is not allowed in after trigger

我也尝试使用 OLD 但也出现错误.我可以在 BEFORE 之前做，但是如果我添加了 100 次点击，它将使用之前的 # 次点击作为触发器(对吗?)

I tried using OLD as well but also got an error. I could do BEFORE but then if I added 100 clicks it would use the previous # clicks for the trigger (right?)

我应该怎么做才能做到这一点?

What should I do to accomplish this?

编辑 - 将在此上运行的查询示例:

EDIT - An example of a query that would be run on this:

UPDATE records SET clicks=clicks+100
//EPC should update automatically

推荐答案

您不能在 after 更新触发器中更新表中的行.

You can't update rows in the table in an after update trigger.

也许你想要这样的东西:

Perhaps you want something like this:

CREATE TRIGGER `records_integrity` BEFORE UPDATE
ON `records`
FOR EACH ROW
    SET NEW.epc=IFNULL(new.earnings/new.clicks, 0);

在触发器内部，您可以访问OLD 和NEW.OLD 是记录中的旧值，NEW 是新值.在 before 触发器中，NEW 值是写入表的内容，因此您可以修改它们.在 after 触发器中，NEW 值已经写入，因此无法修改.我认为 MySQL 文档 很好地解释了这一点.

Inside a trigger, you have have access to OLD and NEW. OLD are the old values in the record and NEW are the new values. In a before trigger, the NEW values are what get written to the table, so you can modify them. In an after trigger, the NEW values have already been written, so they cannot be modified. I think the MySQL documentation explains this pretty well.

相关文章