title: Mysql - dynamic SQL not allowed in trigger
body: <p>I want to create a trigger with dynamic SQL where I get a column name from a variable. Here is my simplified mysql schema:</p>
  
  <pre><code>CREATE TABLE products (id int);
  INSERT INTO products VALUES (1),(2);
  
  CREATE TABLE attribute_values 
  (product_id int, `key` varchar(100), value varchar(100));
  INSERT INTO attribute_values VALUES
  ( 1, 'title', 'Orange'),
  ( 1, 'code', 'O125'),      
  ( 2, 'title', 'Pizza');
  
  
  CREATE TABLE product_attributes 
  SELECT  products.id,
          MAX(CASE WHEN attribute_values.key = 'title' THEN attribute_values.value END) title,
          MAX(CASE WHEN attribute_values.key = 'code' THEN attribute_values.value END) code
  FROM    products JOIN attribute_values ON products.id = attribute_values.product_id
  GROUP   BY products.id;
  
  
  # trigger
  DELIMITER //
  CREATE PROCEDURE attribute_values_after_insert(IN product_id INT, IN column_name VARCHAR(100), IN val VARCHAR(100))
  BEGIN
      SET @sql = NULL;
      SELECT concat('UPDATE product_attributes SET product_attributes.', column_name, '=', val, ' WHERE id=', product_id) INTO @sql;
  
      PREPARE stmt FROM @sql;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
  
      SET @sql = NULL;
  END//
  DELIMITER ;
  
  DELIMITER //
  CREATE TRIGGER attribute_values_insert_trigger AFTER INSERT ON attribute_values FOR EACH ROW
  BEGIN
      CALL attribute_values_after_insert(NEW.product_id, NEW.key, NEW.value);
  END
  DELIMITER ;
  </code></pre>
  
  <p><a href="http://sqlfiddle.com/#!9/674fd/1" rel="nofollow">http://sqlfiddle.com/#!9/674fd/1</a></p>
  
  <p>i.e:</p>
  
  <p>table products:</p>
  
  <pre><code>+-------------+
  | id          |
  +-------------+
  | 1           |
  | 2           |
  +-------------+
  </code></pre>
  
  <p>table attribute_values:</p>
  
  <pre><code>+-------------+----------+-----------+
  | product_id  | key      | value     |
  +-------------+----------+-----------+
  | 1           | title    | Orange    |
  | 1           | code     | O125      |
  | 2           | title    | Pizza     |
  +-------------+----------+-----------+
  </code></pre>
  
  <p>table product_attributes:</p>
  
  <pre><code>+-------------+----------+-----------+
  | id          | title    | code      |
  +-------------+----------+-----------+
  | 1           | Orange   | 0125      |
  | 2           | Pizza    |           |
  +-------------+----------+-----------+
  </code></pre>
  
  <p>In the trigger I want to update table product_attributes after inserting to table attribute_values.</p>
  
  <p>When I execute query:</p>
  
  <pre><code>INSERT INTO attribute_values VALUES (2, 'code', '0126');
  </code></pre>
  
  <p>I get error:</p>
  
  <blockquote>
    <p>DDL and DML statements are not allowed in the query panel for MySQL;
    only SELECT statements are allowed. Put DDL and DML in the schema
    panel.</p>
  </blockquote>
  
  <p>Is there a solution for this in mysql?</p>
  
  <p>Thanks for any efforts :)</p>