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屋！