0

I have a trigger setup that every time a new table is created, it reads the ID (which is in varchar) and converts it to integer and stores it in another column.

However when I run it I encounter the following error:

Can't update table 'products' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

This is my trigger:

CREATE TRIGGER `var_to_int` AFTER INSERT ON `products`
FOR EACH ROW UPDATE `products` 
SET `int_id`= CAST(`var_id` AS INT)
WHERE `int_id` IS NULL OR `int_id` = ''

How do I prevent this?

This error is thrown by the PHP script creating a new item in the table.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Nikk
  • 7,384
  • 8
  • 44
  • 90

1 Answers1

1

You can not update the table itself. Trigger starts when it is UPDATE, and then it has to do UPDATE. An endless loop is formed. Try using an additional table to store temporary data to later update the correct table

starko
  • 1,150
  • 11
  • 26
  • But it's set to `AFTER` the `INSERT`. I just want to `CAST` the `VAR` to `INT`....after. – Nikk Apr 12 '18 at 07:23
  • It does not matter. You can not execute the DML instructions on the table on which the trigger is imposed – starko Apr 12 '18 at 07:24