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?

Answer 1

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