我遇到了一个问题:我想更新一条插入到带有触发器的表中的记录:
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触发器更新插入的行
发布评论