Mysql触发器问题(我认为它正在触发)

Question

我在 mysql 数据库上遇到触发器问题。我有一个如下表:

id int not null auto_increment (PK)
parent_id int not null,
rank int not null

我想做的是，当它们具有相同的parent_id时，使用触发器将排名更新到下一个最高的+10，但这似乎不起作用。

DELIMITER $$
DROP TRIGGER IF EXISTS after_insert $$
create trigger after_insert 
after insert on mytable
FOR EACH row
BEGIN

  IF EXISTS (SELECT rank FROM mytable WHERE parent_id = new.parent_id AND id != new.id ORDER BY rank DESC LIMIT 1) THEN
  UPDATE mytable SET rank  = 10
  WHERE id = new.id;
  ELSE
  UPDATE mytable SET rank = 20
  WHERE id = new.id;
  END IF;   

END
$$

我尝试将新排名设置为变量并使用它调用更新语句，但它再次不起作用。我什至创建了另一个表来记录所选择的值，并且效果很好，所以我不太明白发生了什么。这是一种情况，尽管触发器是“插入后”，但插入实际上并未发生，因此无法更新刚刚插入的行？我问这个问题的另一个原因是，我什至尝试将排名更新为不同的值，例如 1 和 2，具体取决于它所在的语句，但它总是最终为 0。

Answer 1

我认为你的想法是正确的:

Is it a case of, although the trigger is "AFTER INSERT" the insert hasn't actually happened so it can't update the row it's just inserted?

来自FAQ :

B.5.9: Can triggers access tables?

A trigger can access both old and new data in its own table. A trigger can also affect other tables, but it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

文档并不清楚你所做的事情是行不通的。 OTOH，文档并不清楚您想要做的事情是否会起作用。

我认为您最好使用 BEFORE INSERT 触发器并在其中设置 NEW.rank 。然后，新行在实际插入表中时将具有正确的 rank 值，而不是在之后对其进行修补。此外，您还可以将存在检查简化为:

EXISTS(SELECT rank FROM mytable WHERE parent_id = new.parent_id)

as NEW.id 不会有有用的值，并且新行无论如何也不会出现在表中； ORDER BY 和 LIMIT 也是不必要的，因为您只是检查某些内容是否存在，所以我将它们删除了。

无论如何，BEFORE INSERT 触发器似乎更符合您的意图，并且一旦将数据插入表中，它就会为您提供正确的数据。