Using result of SQL Query as table name in mysql trigger

Question

I've to write a trigger on my table which will perform the following functions.

• Before Update on row, check price of Item
• If price has changed from the last price, then select the table name, where to insert the item name, from another table having type of item and the associated table name.
• Insert the item name in the selected table.

To put simply i've a table(TypeNameTable) having item categories and corresponding table names, if the price of item has changed then i've to get the table name from the TypeNameTable and insert the item name in the table, which is retrieved from TypeNameTable. I'm not able to insert into table when I get the table names dynamically. Please suggest how to do it. Here's what I'm doing:

BEGIN

  #declare countryTableName varchar(50);
  declare itemPrice int;
  declare itemTableName text;

  IF (New.Price != Old.Price) THEN
    SET countryTableName = (select `ItemManager`.`TypeNames`.`TypeTableName` 
                              from `ItemManager`.`TypeNames` 
                             where `ItemManager`.`TypeNames`.`ItemType` = NEW.ItemType);

   INSERT INTO `ItemManager`.itemTableName
     ( `ItemName`, `ItemPrice`,
   VALUES
    ( NEW.Name, New.Price );

  END IF;
END$$

I get the error

ItemManager.itemTableName doesn't exists.

Answer 1

Answering my own question.
Figured out that using Dynamic SQL is not allowed in MySQL triggers .
The restrictions are listed here.
However it's possible in Oracle where we can use PRAGMA AUTONOMOUS_TRANSACTION which executes the query in new context, and hence supports Dynamic SQL.
Example listed here at Point 27 .