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粉5738097272023-10-16 17:19:55
The correct syntax is:
FOR EACH ROW SET NEW.bname = CONCAT( UCASE( LEFT( NEW.bname, 1 ) ) , LCASE( SUBSTRING( NEW.bname, 2 ) ) )
P粉9211651812023-10-16 11:22:02
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