MySql Error: The table in a stored function/trigger cannot be updated because it is already used by a statement that calls this stored function/trigger

Question

I'm running a MySQL query. However, when adding a new row from the 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

The correct syntax is:

FOR EACH ROW SET NEW.bname = CONCAT( UCASE( LEFT( NEW.bname, 1 ) )
                                   , LCASE( SUBSTRING( NEW.bname, 2 ) ) )

Answer 2

You cannot alter the table while the INSERT trigger fires. INSERT may perform some locking operations, which may cause deadlock. Additionally, updating the table from a trigger will cause the same trigger to fire again in an infinite recursive loop. Both of these reasons are MySQL reasons that prevent you from doing this.

However, depending on what you want to achieve, you can use NEW.fieldname to access the new value, or even the old value - if you do a UPDATE -- and OLD.

If you have a row called full_brand_name and you want to use the first two letters in the small_name field as the short name, you can 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