Use custom variable in "INSERT ON" clause

Question

I am trying to make a clean trigger with user variables, so this is my code:

---

SET @target_bdd = 'trigger_second'; SET @trigger_name = 'account_copy'; SET @table_name = 'account'; SET @primary_key = 'id'; DROP TRIGGER IF EXISTS `@trigger_name`; DELIMITER $$ CREATE TRIGGER `@trigger_name` AFTER INSERT ON `@table_name` FOR EACH ROW BEGIN INSERT INTO `@target_bdd`.`@table_name` SELECT * FROM `@table_name` WHERE `@primary_key` = NEW.`@primary_key`; END $$ DELIMITER ; 

---

But I have this error:

 ERROR 1064 (42000): 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 '@table_name FOR EACH ROW BEGIN INSERT INTO `@target_bdd`.`@table_name` SELECT * ' at line 2 

Why? I tried with other quotes ('"), but there is the same error: (

Without a quote:

ERROR 1064 (42000): You have an error in the SQL syntax; check the manual that matches your version of MySQL server for the correct syntax to use next to the name @trigger_ AFTER INSERT ON @table_name FOR EACH ROW BEGIN INSERT INTO @target 'on line 1

I am trying to declare a custom variable with SQL statements, for example:

 SET @sql = CONCAT( 'CREATE TRIGGER ', @trigger_name, ' AFTER INSERT ON ', @table_name, ' FOR EACH ROW BEGIN INSERT INTO ', @target_bdd, '.', @table_name, ' SELECT * FROM ', @table_name, ' WHERE ', @primary_key, ' = NEW.', @primary_key, '; END' ); PREPARE stmt FROM @sql; EXECUTE stmt; 

But I have error 1295 ( See MySQL documentation )

Answer 1

You cannot use user variables for table names, column names, etc. in plain SQL. You also cannot create a trigger in a prepared message (just like you reported an error). It seems that there is currently no way to do what you want.

Answer 2

I have to say that I have never used custom variables before, but when testing, I found out that you should not quote them! When you specify `@ vars`, mysql will look for the '@vars' , instead of the value that is stored in it.