Error: Dynamic SQL is not allowed in stored function or trigger

Question

Im trying to convert multivalued column in Table A into rows of Table B(The data type of the column is varchar). For ex:

Table A

id  value
ae  [1,2,4,5]
ac  [1,6]

Table B

id  value
ae   1
ae   2
ae   4
ae   5
ac   1
ac   6

I wrote an after insert trigger on Table A

CREATE DEFINER = CURRENT_USER TRIGGER `p`.`table_A_AFTER_INSERT` AFTER INSERT ON `table_A` FOR 
EACH ROW
BEGIN

    drop temporary table if exists p.temp;

    create temporary table p.temp( val char(255) );

    set @sql = concat("insert into p.test (val) values ('", replace(( selectgroup_concat(distinct 
    txt) as data from pmo.t), ",", "'),('"),"');");

    prepare stmt1 from @sql;

    execute stmt1;

    insert into p.table_B (val )

    select val

    from p.temp;

END

---

I encounter error

Error 1336: Dynamic SQL is not allowed in stored function or trigger SQL Statement

What is the way to work around this?

Answer 1

With mariadb or mysql upwards from version 8, it would be easier:

CREATE TABLE amv (
  id varchar(2) primary key,
  mvc varchar(255)
);
INSERT INTO amv  VALUES ('ae','[1,2,4,5]');
INSERT INTO amv  VALUES ('ac','[1,6]');

Create table bsv as
  SELECT amv.id, jt.sv FROM amv,
 Json_table(amv.mvc,
            "$[*]" COLUMNS(sv int path '$')) jt;

See db fiddle

Answer 2

I solved this by referring to https://stackoverflow.com/a/11835246/8751454

My function definition:

CREATE DEFINER=`root`@`localhost` FUNCTION `SPLIT_STR`(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
) RETURNS varchar(255) CHARSET utf8mb4
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '')

My stored procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `ABC`(fullstr varchar(100))
BEGIN
      DECLARE a INT Default 0 ;
      DECLARE str VARCHAR(255);
      drop temporary table if exists pmo.temp;
      simple_loop: LOOP
         SET a=a+1;
         SET str=SPLIT_STR(fullstr,",",a);
         IF str='' THEN
            LEAVE simple_loop;
         END IF;
 
         create temporary table temp( val char(255) );

         insert into pmo.temp(val) values (str);
         insert into pmo.meet(val)
         select val 
         from pmo.temp;
         drop temporary table if exists pmo.temp;
   END LOOP simple_loop;
   END

My after insert trigger on Table A

CREATE DEFINER=`root`@`localhost` TRIGGER `templates_AFTER_INSERT` AFTER INSERT 
ON `templates` FOR EACH ROW BEGIN
    declare multi_column_data varchar(100);
    set @multi_column_data = replace(new.fieldvalue,' , ',',');
    call ABC(@multi_column_data);

END