MySql 错误：无法更新存储函数/触发器中的表，因为它已被调用此存储函数/触发器的语句使用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15300673/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySql Error: Can't update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger
提问by Mitesh Mynee
I am running a MySQL Query. But when a new row is added from form input I get this error:
我正在运行一个 MySQL 查询。但是当从表单输入添加新行时，我收到此错误：
Error: Can't update table 'brandnames' in stored function/trigger because it is
already used by statement which invoked this stored function/trigger.
From the code:
从代码：
CREATE TRIGGER `capital` AFTER INSERT ON `brandnames`
FOR EACH
ROW UPDATE brandnames
SET bname = CONCAT( UCASE( LEFT( bname, 1 ) ) , LCASE( SUBSTRING( bname, 2 ) ) )
What does this error mean?
这个错误是什么意思？
回答by Steve
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.
当 INSERT 触发器触发时，您不能更改表。INSERT 可能会进行一些锁定，这可能会导致死锁。此外，从触发器更新表会导致相同的触发器在无限递归循环中再次触发。这两个原因都是 MySQL 阻止您这样做的原因。
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.
但是，根据您要实现的目标，您可以使用 NEW.fieldname 或什至旧值（如果执行 UPDATE）访问新值（如果使用 OLD）。
If you had a row named full_brand_nameand you wanted to use the first two letters as a short name in the field small_nameyou could use:
如果您有一行命名full_brand_name并且您想使用前两个字母作为字段中的短名称，small_name您可以使用：
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
回答by rsanchez
The correct syntax is:
正确的语法是：
FOR EACH ROW SET NEW.bname = CONCAT( UCASE( LEFT( NEW.bname, 1 ) )
, LCASE( SUBSTRING( NEW.bname, 2 ) ) )
回答by gerrit_hoekstra
A "BEFORE-INSERT"-trigger is the only way to realize same-table updates on an insert, and is only possible from MySQL 5.5+. However, the value of an auto-increment field is only available to an "AFTER-INSERT" trigger - it defaults to 0 in the BEFORE-case. Therefore the following example code which would set a previously-calculated surrogate key value based on the auto-increment value idwill compile, but not actually work since NEW.id will always be 0:
“BEFORE-INSERT”触发器是在插入时实现同表更新的唯一方法，并且只能从 MySQL 5.5+ 开始。但是，自动增量字段的值仅适用于“AFTER-INSERT”触发器 - 在 BEFORE 情况下默认为 0。因此，以下示例代码将根据自动增量值设置先前计算的代理键值id，但实际上不会工作，因为 NEW.id 将始终为 0：
create table products(id int not null auto_increment, surrogatekey varchar(10), description text);
create trigger trgProductSurrogatekey before insert on product
for each row set NEW.surrogatekey =
(select surrogatekey from surrogatekeys where id = NEW.id);
回答by B?o Nam
I have the same problem and fix by add "new." before the field is updated. And I post full trigger here for someone to want to write a trigger
我有同样的问题并通过添加“新”来修复。在字段更新之前。我在这里发布了完整的触发器，供想要编写触发器的人使用
DELIMITER $$
USE `nc`$$
CREATE
TRIGGER `nhachung_province_count_update` BEFORE UPDATE ON `nhachung`
FOR EACH ROW BEGIN
DECLARE slug_province VARCHAR(128);
DECLARE slug_district VARCHAR(128);
IF old.status!=new.status THEN /* neu doi status */
IF new.status="Y" THEN
UPDATE province SET `count`=`count`+1 WHERE id = new.district_id;
ELSE
UPDATE province SET `count`=`count`-1 WHERE id = new.district_id;
END IF;
ELSEIF old.province_id!=new.province_id THEN /* neu doi province_id + district_id */
UPDATE province SET `count`=`count`+1 WHERE id = new.province_id; /* province_id */
UPDATE province SET `count`=`count`-1 WHERE id = old.province_id;
UPDATE province SET `count`=`count`+1 WHERE id = new.district_id; /* district_id */
UPDATE province SET `count`=`count`-1 WHERE id = old.district_id;
SET slug_province = ( SELECT slug FROM province WHERE id= new.province_id LIMIT 0,1 );
SET slug_district = ( SELECT slug FROM province WHERE id= new.district_id LIMIT 0,1 );
SET new.prov_dist_url=CONCAT(slug_province, "/", slug_district);
ELSEIF old.district_id!=new.district_id THEN
UPDATE province SET `count`=`count`+1 WHERE id = new.district_id;
UPDATE province SET `count`=`count`-1 WHERE id = old.district_id;
SET slug_province = ( SELECT slug FROM province WHERE id= new.province_id LIMIT 0,1 );
SET slug_district = ( SELECT slug FROM province WHERE id= new.district_id LIMIT 0,1 );
SET new.prov_dist_url=CONCAT(slug_province, "/", slug_district);
END IF;
END;
$$
DELIMITER ;
Hope this help someone
希望这有助于某人
回答by rf1234
@gerrit_hoekstra wrote: "However, the value of an auto-increment field is only available to an "AFTER-INSERT" trigger - it defaults to 0 in the BEFORE-case."
@gerrit_hoekstra 写道：“但是，自动增量字段的值仅适用于“AFTER-INSERT”触发器——在 BEFORE 情况下它默认为 0。
That is correct but you can select the auto-increment field value that will be inserted by the subsequent INSERT quite easily. This is an example that works:
这是正确的，但您可以很容易地选择将由后续 INSERT 插入的自动增量字段值。这是一个有效的例子：
CREATE DEFINER = CURRENT_USER TRIGGER `lgffin`.`variable_BEFORE_INSERT` BEFORE INSERT
ON `variable` FOR EACH ROW
BEGIN
SET NEW.prefixed_id = CONCAT(NEW.fixed_variable, (SELECT `AUTO_INCREMENT`
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'lgffin'
AND TABLE_NAME = 'variable'));
END
