MySql 错误:无法更新存储函数/触发器中的表,因为它已被调用此存储函数/触发器的语句使用

MySql Error: Can#39;t update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger(MySql 错误:无法更新存储函数/触发器中的表,因为它已被调用此存储函数/触发器的语句使用) - IT屋-程
本文介绍了MySql 错误:无法更新存储函数/触发器中的表,因为它已被调用此存储函数/触发器的语句使用的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

问题描述

我正在运行一个 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 错误:无法更新存储函数/触发器中的表,因为它已被调用此存储函数/触发器的语句使用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

相关文档推荐

Can#39;t Create Entity Data Model - using MySql and EF6(无法创建实体数据模型 - 使用 MySql 和 EF6)
MySQL select with CONCAT condition(MySQL选择与CONCAT条件)
Capitalize first letter of each word, in existing table(将现有表格中每个单词的首字母大写)
How to retrieve SQL result column value using column name in Python?(如何在 Python 中使用列名检索 SQL 结果列值?)
Update row with data from another row in the same table(使用同一表中另一行的数据更新行)
Exporting results of a Mysql query to excel?(将 Mysql 查询的结果导出到 excel?)