Within a mysql trigger, I need to loop through a comma-separated list and perform a query for each item on the list
DELIMITER $$
DROP trigger IF EXISTS my_trigger $$
CREATE TRIGGER my_trigger AFTER UPDATE ON my_table
FOR EACH ROW BEGIN
SET @id = NEW.id;
SET @item_str = NEW.item_str; -- This is a comma separated list of integers like "34,1,200,99"
/*DYNAMIC SECTION*/
SET @item_list = CONCAT('''',REPLACE(REPLACE(@item_str ,' ',''),',',QUOTE(',')),'''');
SET @list_stub = REPLACE(@item_list,',','');
SET @list_count = LENGTH(@item_list) - LENGTH(@list_stub) + 1;
-- Loop through list of items
SET @x = 0;
WHILE @x < @list_count DO
SET @x = @x + 1;
SET @sql = CONCAT('SELECT ELT(',@x,',',@item_list,') INTO @item');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
SET @sql = CONCAT('INSERT IGNORE INTO new_table (id, item) VALUES(', @id, ',', @item, ')' );
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
END WHILE;
END $$
DELIMITER ;
This gives me the error:
ERROR 1336 (0A000): Dynamic SQL is not allowed in stored function or trigger
Since the "Dynamic Section" had worked well in a stored procedure, I tried moving that portion to a separated procedure, then called it from this trigger:
Same error!
The closest question I found on the subject (Loop in trigger with comma separated values mysql) looked very lean and unhelpful. The link included in that answer is infact dead. In any case, it uses creating of temporary table which I suspect would throw up permission issues with this user.
Is there at least a workaround? How can I loop through this list and perform a query with each value?
