Using a query in ALTER TABLE in CREATE TRIGGER

Question

I'm trying to create a dynamic ALTER TABLE command but some of the command will be generated from a query. The problem is that I want to use this in a trigger!

Attempt 1:

ALTER TABLE `redinfomanager` CHANGE `Unterkategorie` `Unterkategorie` ENUM(("SELECT GROUP_CONCAT(CONCAT('\'', REPLACE(`Unterkategorien`, '\r\n', '\',\''), '\'') SEPARATOR  ',') FROM `kategorien` GROUP BY '1'")) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;

Attempt 2:

SELECT @tmp:=GROUP_CONCAT(CONCAT('\'', REPLACE(`Unterkategorien`, '\r\n', '\',\''), '\'') SEPARATOR  ',') FROM `kategorien` GROUP BY '1';
SET @query=CONCAT('ALTER TABLE `redinfomanager` CHANGE `Unterkategorie` `Unterkategorie` ENUM(', @tmp, ') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL');
PREPARE stmt FROM @query;
EXECUTE stmt;

Attempt 3:

SET @kat = (SELECT GROUP_CONCAT(CONCAT('\'', REPLACE(`Unterkategorien`, '\r\n', '\',\''), '\'') SEPARATOR  ',') FROM kategorien GROUP BY '1');
PREPARE stmt FROM 'ALTER TABLE redinfomanager CHANGE Unterkategorie Unterkategorie ENUM(?) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL';
EXECUTE stmt USING @kat;

It tells me:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; PREPARE stmt FROM 'ALTER TABLE redinfomanager CHANGE Unterkategorie `Unte' at line 1

How could I do that?

And what does that error stand for?

Answer 1

It seems that you're changing an ENUM value on a second table after every insert on the first table? Why not just make that a foreign key constraint. That will require whatever values you want to put in table 2 will have to have a value from table 1. It will be much more efficient, too:

Just an 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 INTO category (description) 
VALUES 
   ('Category 1')
  ,('Category 2');

INSERT INTO thing (cat_id, description)
VALUES 
   (1, 'Thing 1')
  ,(1, 'Thing 2')
  ,(2, 'Thing 3');

INSERT INTO thing (cat_id, description)
VALUES
   (3, 'Imma Fail!')

If you run it, the third insert will fail, because there's not an id of 3 in the category table.