mysql - Using a query in ALTER TABLE in CREATE TRIGGER -

i'm trying create dynamic alter table command of command generated query. problem want use in trigger!

attempt 1:

alter table `redinfomanager` change `unterkategorie` `unterkategorie` enum(("select group_concat(concat('\'', replace(`unterkategorien`, '\r\n', '\',\''), '\'') separator  ',') `kategorien` group '1'")) character set latin1 collate latin1_swedish_ci not null; 

attempt 2:

select @tmp:=group_concat(concat('\'', replace(`unterkategorien`, '\r\n', '\',\''), '\'') separator  ',') `kategorien` group '1'; set @query=concat('alter table `redinfomanager` change `unterkategorie` `unterkategorie` enum(', @tmp, ') character set latin1 collate latin1_swedish_ci not null'); prepare stmt @query; execute stmt; 

attempt 3:

set @kat = (select group_concat(concat('\'', replace(`unterkategorien`, '\r\n', '\',\''), '\'') separator  ',') kategorien group '1'); prepare stmt 'alter table redinfomanager change unterkategorie unterkategorie enum(?) character set latin1 collate latin1_swedish_ci not null'; execute stmt using @kat; 

it tells me:

1064 - have error in sql syntax; check manual corresponds mysql server version right syntax use near '; prepare stmt 'alter table redinfomanager change unterkategorie `unte' @ line 1

how that?

and error stand for?

it seems you're changing enum value on second table after every insert on first table? why not make foreign key constraint. require whatever values want put in table 2 have have value table 1. more efficient, too:

just example (sqlfiddle link):

create table category (   id int primary key not null auto_increment,   description varchar(50) );  create table thing (   id int primary key not null auto_increment,   cat_id integer,   description varchar(50),   foreign key (cat_id) references category(id) );  insert category (description)  values     ('category 1')   ,('category 2');  insert thing (cat_id, description) values     (1, 'thing 1')   ,(1, 'thing 2')   ,(2, 'thing 3');  insert thing (cat_id, description) values    (3, 'imma fail!') 

if run it, third insert fail, because there's not id of 3 in category table.