MySql Error Can t update table in stored function trigger because it is already used by statement which invoked this stored function trigger

Question

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

User · Answer

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 id will compile  but not actually work since NEW id will always be 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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

The correct syntax is   FOR EACH ROW SET NEW bname   CONCAT  UCASE  LEFT  NEW bname  1                                          LCASE  SUBSTRING  NEW bname  2

[mysql] MySql Error: Can't update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger

Examples related to mysql

Examples related to triggers