触发器中UPDATE之后更新同一行 [英] UPDATE Same Row After UPDATE in Trigger

问题描述

我希望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，但也出现了错误.我可以在此之前做，但是如果我添加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

推荐答案

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

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值是写入表中的值，因此您可以对其进行修改.在之后触发器中，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.

这篇关于触发器中UPDATE之后更新同一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！