74

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?

Aaron J Spetner
  • 2,117
  • 1
  • 18
  • 30
Mitesh Mynee
  • 757
  • 1
  • 6
  • 10
  • what are you trying to do with your trigger? Table schema, example data and example INSERT query would help a lot – Steve Mar 08 '13 at 18:22
  • I want to change the case to upper case of an enrered row value. So I want this trigger to automatically do this for me everytime a row is entered. I don't want javascript. – Mitesh Mynee Mar 08 '13 at 18:30
  • @EricLeschinski I don't know what he did on that question. – Mitesh Mynee Mar 08 '13 at 18:32

5 Answers5

97

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
Eduardo Cuomo
  • 17,828
  • 6
  • 117
  • 94
Steve
  • 3,673
  • 1
  • 19
  • 24
  • 2
    As referenced in another SO question (http://stackoverflow.com/questions/1582683/mysql-trigger-stored-trigger-is-already-used-by-statement-which-invoked-stored-t), MySQL doesn't (yet?) support this feature (http://forums.mysql.com/read.php?99,122354,240978#msg-240978), even though its competitors do. – Eric L. Jun 18 '13 at 18:46
  • @Steve I am getting the same problem. when I am trying: update employee_audit set lastName = 'Sharma' where employeeNumber =112; – Onic Team Jul 10 '17 at 08:43
  • i would like to implement for diff duration clock , thanks ... – Yogi Arif Widodo Jul 25 '22 at 03:57
  • but dunno why didnt work -> update the field of NEW.short_name hm ```delimiter // CREATE TRIGGER person_bi BEFORE INSERT ON person FOR EACH ROW IF NEW.clock_out_time != null THEN SET NEW.name='helo'; END IF; // delimiter ;``` – Yogi Arif Widodo Jul 25 '22 at 04:08
9

The correct syntax is:

FOR EACH ROW SET NEW.bname = CONCAT( UCASE( LEFT( NEW.bname, 1 ) )
                                   , LCASE( SUBSTRING( NEW.bname, 2 ) ) )
rsanchez
  • 14,467
  • 1
  • 35
  • 46
5

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);
gerrit_hoekstra
  • 509
  • 6
  • 8
3

@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
rf1234
  • 1,510
  • 12
  • 13
2

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

Nam Nguyen
  • 2,438
  • 21
  • 18