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.

You have enough rep to know how to use [markdown](http://stackoverflow.com/editing-help) rather than HTML — OMG Ponies, Jul 30 '11 at 05:11
@OMG Ponies: Sorry for the poor formatting. I'm really stuck with the problem here and can't think of anything else. — Amresh Kumar, Jul 30 '11 at 05:16

score 2 · Accepted Answer · answered Aug 01 '11 at 12:29

2

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 .

answered Aug 01 '11 at 12:29

Amresh Kumar

1,445
17
30

You asked about MySQL, but your answer is about Oracle. – Devart Sep 28 '12 at 12:54
@Devart I've told that its not possible in MySql but Oracle supports Dynamic SQL. – Amresh Kumar Oct 01 '12 at 06:00
So, you had wanted to use MySQL, but then chose Oracle. But, why don't you want to change database design? If it is possible - do not store table names in rows. Use table relations. – Devart Oct 01 '12 at 06:32

score 0 · Answer 2 · answered Jul 30 '11 at 05:37

0

You could CONCAT() your INSERT statement into a variable and execute that as PREPARED STATEMENT, someting like

...
SET @sql := CONCAT( 'INSERT INTO ', itemTableName, ' ... ' );
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
...

afaik this is the only way to process dynamically generated SQL in stored routines and triggers.

answered Jul 30 '11 at 05:37

wonk0

13,402
1
21
15

I get the following error when using like this:ERROR 1336: Dynamic SQL is not allowed in stored function or trigger – Amresh Kumar Jul 30 '11 at 05:59
hm, I was not aware this is limited to STORED PROCEDUREs; sorry, I have no idea in that case – wonk0 Jul 30 '11 at 08:01
MYSQL triggers cannot execute Dynamic SQL statements. Even if the statement is being fired from a stored procedure that the trigger is calling. – Sep 27 '12 at 18:25

score -1 · Answer 3 · answered Aug 05 '11 at 06:26

If it is possible, I'd suggest you to change design a little. Instead of different tables you can create one table itemTable.

...
IF (New.Price != Old.Price) THEN

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

END IF;
...

If there are different item properties, this table can be a parent table for specific child tables.

Using result of SQL Query as table name in mysql trigger

3 Answers3