MySql 错误:无法更新存储函数/触发器中的表，因为它已被调用此存储函数/触发器的语句使用 [英] MySql Error: Can't update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger

问题描述

我正在运行一个 MySQL 查询.但是当从表单输入添加新行时，我收到此错误:

I am running a MySQL Query. But when a new row is added from form input I get this error:

Error: Can't update table 'brandnames' in stored function/trigger because it is 
already used by statement which invoked this stored function/trigger.

来自代码:

CREATE TRIGGER `capital` AFTER INSERT ON `brandnames`
FOR EACH
ROW UPDATE brandnames
SET bname = CONCAT( UCASE( LEFT( bname, 1 ) ) , LCASE( SUBSTRING( bname, 2 ) ) )

这个错误是什么意思?

推荐答案

在触发 INSERT 触发器时不能更改表.INSERT 可能会进行一些锁定，这可能会导致死锁.此外，从触发器更新表会导致相同的触发器在无限递归循环中再次触发.这两个原因都是 MySQL 阻止您这样做的原因.

You cannot change a table while the INSERT trigger is firing. The INSERT might do some locking which could result in a deadlock. Also, updating the table from a trigger would then cause the same trigger to fire again in an infinite recursive loop. Both of these reasons are why MySQL prevents you from doing this.

但是，根据您要实现的目标，您可以使用 NEW.fieldname 或什至旧值(如果执行 UPDATE)访问新值 - 使用 OLD.

However, depending on what you're trying to achieve, you can access the new values by using NEW.fieldname or even the old values--if doing an UPDATE--with OLD.

如果您有一行名为 full_brand_name 并且您想在字段 small_name 中使用前两个字母作为短名称，您可以使用:

If you had a row named full_brand_name and you wanted to use the first two letters as a short name in the field small_name you could use:

CREATE TRIGGER `capital` BEFORE INSERT ON `brandnames`
FOR EACH ROW BEGIN
  SET NEW.short_name = CONCAT(UCASE(LEFT(NEW.full_name,1)) , LCASE(SUBSTRING(NEW.full_name,2)))
END

这篇关于MySql 错误:无法更新存储函数/触发器中的表，因为它已被调用此存储函数/触发器的语句使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！