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-PHP Chinese Network Q&A

Article Topic Learning Download Q&A Programming Dictionary Game Recent Updates

简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW)

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

P粉668804228 2023-10-15 23:34:43

471

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?

P粉668804228

reply all(2)

P粉5738097272023-10-16 17:19:55 2 floor

The correct syntax is:

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

Like +0

Add Reply

P粉9211651812023-10-16 11:22:02 1 floor

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

Like +0

Add Reply