0

I created a trigger which throws an error whenever new entry is stored. Here is the trigger:

CREATE TRIGGER `htmlid` BEFORE INSERT ON `makelist_food`
FOR EACH ROW BEGIN
IF (NEW.html_id IS NULL) THEN
  INSERT INTO makelist_food SET html_id = CONCAT('f', NEW.id);
END IF;
END

And the error is: #1442 - Can't update table 'makelist_food' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

There are many discussions out there about this error but I haven't found any solution or workaround. Here is an answer from 2010 stackoverflow.com/questions/1582683/mysql-trigger-stored-trigger-is-already-used-by-statement-which-invoked-stored-t which states that this is simply not supported in MySQL.

Since this is more than 2 years old answer I was wondering if SO knows some workaround for this problem. I couldn't find any.

Community
  • 1
  • 1
finspin
  • 4,021
  • 6
  • 38
  • 66

1 Answers1

1

Do you by any chance mean 

IF (NEW.html_id IS NULL) THEN
    SET NEW.html_id = CONCAT('f', NEW.id);
END IF;
Vatev
  • 7,493
  • 1
  • 32
  • 39
  • You are right, that was the problem. Thanks! There is an issue with this trigger though. It always updates html_id field to the value f0. I expected it would pick up the id of a newly created record and concatenate it with f. But I guess this is a whole new question. – finspin Jul 29 '12 at 19:37
  • I don't think that would be possible, the auto_increment id does't exist yet at that point. Take a look at [this question](http://stackoverflow.com/questions/1211792/mysql-trigger-to-update-a-field-to-the-value-of-id) – Vatev Jul 29 '12 at 19:42
  • You might be able to hack it up using a 'manual auto increment' but that might slow it down and create other problems. – Vatev Jul 29 '12 at 19:48