Mysql触发器更新插入的行 [英] Mysql trigger to update the inserted row

问题描述

我遇到了一个问题:我想更新一条插入到带有触发器的表中的记录:

I'm facing a problem: I want to update a record that being inserted to a table with trigger:

DELIMITER $$
CREATE TRIGGER moodle.update_lang
AFTER INSERT
ON moodle.mdl_user FOR EACH ROW
BEGIN
   update moodle.mdl_user SET lang='hu' WHERE lang='en';
END$$
DELIMITER ;

所以当我现在想插入一行时，它给了我一个错误，无法插入该行，因为某个过程已经在使用它.你能给出任何解决方案如何使这项工作?这种方法是否可用，或者我应该从不同的角度解决问题吗?提前致谢！

So when I want to insert a row now, it gives me an error that the row can't be inserted because a procedure is already using it. Can you give any solutions how to make this work? Is this method usable or should I aproach the problem from a different angle? Thanks in advance!

推荐答案

根据评论 :)

您需要一个 BEFORE INSERT 触发器.在该触发器中，您可以在记录到达永久存储之前更改记录.使用您的示例，此触发器的定义如下:

You require a BEFORE INSERT trigger. In that trigger, you alter the record before it reaches permanent storage. Using your example, this trigger would be defined like this:

DELIMITER $$
CREATE TRIGGER moodle.update_lang
BEFORE INSERT
ON moodle.mdl_user FOR EACH ROW
BEGIN
   SET NEW.lang='hu';
END$$
DELIMITER ;

您不能在触发器引用的同一个表上使用 UPDATE 的原因是因为这可能(并且会)导致无限循环.

The reason you can't use UPDATE on the same table that trigger refers to is because that could (and would) cause an infinite loop.

注意:我还没有测试过这个，但从你的评论来看它似乎有效.祝你好运！

Note: I haven't tested this, but judging by your comments it seems to be working. Good luck!

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