1

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?

0

2 Answers 2

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

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.