Mysql触发器更新插入的行

本文介绍了Mysql触发器更新插入的行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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!