How can I create "dynamic" trigger where variable is table name ? First I wanted to create database trigger in procedure, but I read that mysql is not supported triggers from prepared statements :-( For this reason I need to create sql query from application, but there is a problem..

Trigger looks like that (listing 1. at the end of thread)
After simplification (removed new lines) it looks like:

Qt Code: 
  1. #Listing 2.
  2. DROP TRIGGER IF EXISTS `insertPoints`; #new line
  3. DELIMITER $$ #new line
  4. CREATE TRIGGER `insertPoints` BEFORE INSERT ON `points`FOR EACH ROW BEGIN UPDATE results SET totalPoints=totalPoints+NEW.val WHERE res_id=NEW.res_id; IF NEW.val >= 10 THEN UPDATE results SET num_10=num_10+1 WHERE res_id=NEW.res_id; ELSE IF NEW.val BETWEEN 9 AND 10 THEN UPDATE results SET num_9=num_9+1 WHERE res_id=NEW.res_id; ELSE IF NEW.val BETWEEN 8 AND 9 THEN UPDATE results SET num_8=num_8+1 WHERE res_id=NEW.res_id; ELSE IF NEW.val BETWEEN 7 AND 8 THEN UPDATE results SET num_7=num_7+1 WHERE res_id=NEW.res_id; ELSE IF NEW.val BETWEEN 6 AND 7 THEN UPDATE results SET num_6=num_6+1 WHERE res_id=NEW.res_id; ELSE IF NEW.val BETWEEN 5 AND 6 THEN UPDATE results SET num_5=num_5+1 WHERE res_id=NEW.res_id; ELSE IF NEW.val BETWEEN 4 AND 5 THEN UPDATE results SET num_4=num_4+1 WHERE res_id=NEW.res_id; ELSE IF NEW.val BETWEEN 3 AND 4 THEN UPDATE results SET num_3=num_3+1 WHERE res_id=NEW.res_id; ELSE IF NEW.val BETWEEN 2 AND 3 THEN UPDATE results SET num_2=num_2+1 WHERE res_id=NEW.res_id; ELSE IF NEW.val BETWEEN 1 AND 2 THEN UPDATE results SET num_1=num_1+1 WHERE res_id=NEW.res_id; ELSE IF NEW.val BETWEEN 0 AND 1 THEN UPDATE results SET num_0=num_0+1 WHERE res_id=NEW.res_id;END IF; END IF; END IF; END IF; END IF; END IF; END IF; END IF; END IF; END IF;END IF;
  5. END $$ #new line
To copy to clipboard, switch view to plain text mode 

comment: where I noticed that only the query executed directly on the database (mysql workbench) is appropriate .. must be new lines in those places where I wrote .. As a consequence of I've prepared the following query in C + + / Qt:

Qt Code: 
  1. QString tableResult = "results"+ctID;
  2.     QString .tablePoint = "points"+ctID;
  3.     QString triggerName = "insertPoints"+ctID;
  4.  
  5.     QString Query =  QString("DROP TRIGGER IF EXISTS "+triggerName+"; ") +
  6.                     QString("\n") +
  7.                     QString("DELIMITER $$  ") +
  8.                     QString("\n") +
  9.                     QString("CREATE TRIGGER "+triggerName+" BEFORE INSERT ON "+.tablePoint+" ") +
  10.                     QString("FOR EACH ROW BEGIN ") +
  11.                     QString("UPDATE "+tableResult+" SET totalPoints=totalPoints+NEW.val WHERE res_id=NEW res_id; ") +
  12.                     QString("if NEW.val >= 10 then ") +
  13.                     QString("UPDATE "+tableResult+" SET num_10=num_10+1 WHERE res_id=NEW.res_id; ") +
  14.                     QString("else ") +
  15.                     QString("if NEW.val between 9 and 10 then ") +
  16.                     QString("UPDATE "+tableResult+" SET num_9=num_9+1 WHERE res_id=NEW.res_id; ") +
  17.                     QString("else ") +
  18.                     QString("if NEW.val between 8 and 9 then ") +
  19.                     QString("UPDATE "+tableResult+" SET num_8=num_8+1 WHERE res_id=NEW.res_id; ") +
  20.                     QString("else ") +
  21.                     QString("if NEW.val between 7 and 8 then ") +
  22.                     QString("UPDATE "+tableResult+" SET num_7=num_7+1 WHERE res_id=NEW.res_id; ") +
  23.                     QString("else ") +
  24.                     QString("if NEW.val between 6 and 7 then ") +
  25.                     QString("UPDATE "+tableResult+" SET num_6=num_6+1 WHERE res_id=NEW.res_id; ") +
  26.                     QString("else ") +
  27.                     QString("if NEW.val between 5 and 6 then ") +
  28.                     QString("UPDATE "+tableResult+" SET num_5=num_5+1 WHERE res_id=NEW.res_id; ") +
  29.                     QString("else ") +
  30.                     QString("if NEW.val between 4 and 5 then ") +
  31.                     QString("UPDATE "+tableResult+" SET num_4=num_4+1 WHERE res_id=NEW.res_id; ") +
  32.                     QString("else ") +
  33.                     QString("if NEW.val between 3 and 4 then ") +
  34.                     QString("UPDATE "+tableResult+" SET num_3=num_3+1 WHERE res_id=NEW.res_id; ") +
  35.                     QString("else ") +
  36.                     QString("if NEW.val between 2 and 3 then ") +
  37.                     QString("UPDATE "+tableResult+" SET num_2=num_2+1 WHERE res_id=NEW.res_id; ") +
  38.                     QString("else ") +
  39.                     QString("if NEW.val between 1 and 2 then ") +
  40.                     QString("UPDATE "+tableResult+" SET num_1=num_1+1 WHERE res_id=NEW.res_id; ") +
  41.                     QString("else ") +
  42.                     QString("if NEW.val between 0 and 1 then ") +
  43.                     QString("UPDATE "+tableResult+" SET num_0=num_0+1 WHERE res_id=NEW.res_id; ") +
  44.                     QString("end if; ") +
  45.                     QString("end if; ") +
  46.                     QString("end if; ") +
  47.                     QString("end if; ") +
  48.                     QString("end if; ") +
  49.                     QString("end if; ") +
  50.                     QString("end if; ") +
  51.                     QString("end if; ") +
  52.                     QString("end if; ") +
  53.                     QString("end if; ") +
  54.                     QString("end if; ") +
  55.                     QString("\n") +
  56.                     QString("END $$ ") ;
To copy to clipboard, switch view to plain text mode 

and execute the query :

Qt Code: 
  1. QSqlQuery Query;
  2.  Query.exec(Query);
To copy to clipboard, switch view to plain text mode 

conclusions/problems:

Executed the query does not make an error, but the trigger isn't added to the table..

Other, if I removed firstly two lines QString ("DROP TRIGGER IF EXISTS" + triggername + ";") + QString ("\ n") + gets a syntax error looks like: (btw I dont know knowing why it even occurs..)

Qt Code: 
  1. "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 'DELIMITER $$  CREATE TRIGGER insertPoints_24 BEFORE INSERT ON points_24 FOR E' at line 1 QMYSQL: Unable to execute query"
To copy to clipboard, switch view to plain text mode 

---------------------------------------
Qt Code: 
  1. #Listing 1.
  2. DROP TRIGGER IF EXISTS `insertPoints`;
  3. DELIMITER $$
  4. CREATE TRIGGER `insertPoints` BEFORE INSERT ON `points`
  5.   FOR EACH ROW BEGIN
  6.     UPDATE results SET totalPoints=totalPoints+NEW.val WHERE res_id=NEW.res_id;
  7.  
  8.       IF NEW.val >= 10 THEN
  9.         UPDATE results SET num_10=num_10+1 WHERE res_id=NEW.res_id;
  10.     ELSE
  11.         IF NEW.val BETWEEN 9 AND 10 THEN
  12.             UPDATE results SET num_9=num_9+1 WHERE res_id=NEW.res_id;
  13.         ELSE
  14.             IF NEW.val BETWEEN 8 AND 9 THEN
  15.                 UPDATE results SET num_8=num_8+1 WHERE res_id=NEW.res_id;
  16.             ELSE
  17.                 IF NEW.val BETWEEN 7 AND 8 THEN
  18.                     UPDATE results SET num_7=num_7+1 WHERE res_id=NEW.res_id;
  19.                 ELSE
  20.                     IF NEW.val BETWEEN 6 AND 7 THEN
  21.                         UPDATE results SET num_6=num_6+1 WHERE res_id=NEW.res_id;
  22.                     ELSE
  23.                         IF NEW.val BETWEEN 5 AND 6 THEN
  24.                             UPDATE results SET num_5=num_5+1 WHERE res_id=NEW.res_id;
  25.                         ELSE
  26.                             IF NEW.val BETWEEN 4 AND 5 THEN
  27.                                 UPDATE results SET num_4=num_4+1 WHERE res_id=NEW.res_id;
  28.                             ELSE
  29.                                 IF NEW.val BETWEEN 3 AND 4 THEN
  30.                                     UPDATE results SET num_3=num_3+1 WHERE res_id=NEW.res_id;
  31.                                 ELSE
  32.                                     IF NEW.val BETWEEN 2 AND 3 THEN
  33.                                         UPDATE results SET num_2=num_2+1 WHERE res_id=NEW.res_id;
  34.                                     ELSE
  35.                                         IF NEW.val BETWEEN 1 AND 2 THEN
  36.                                             UPDATE results SET num_1=num_1+1 WHERE res_id=NEW.res_id;
  37.                                         ELSE
  38.                                             IF NEW.val BETWEEN 0 AND 1 THEN
  39.                                                 UPDATE results SET num_0=num_0+1 WHERE res_id=NEW.res_id;
  40.                                             END IF;
  41.                                         END IF;
  42.                                     END IF;
  43.                                 END IF;
  44.                             END IF;
  45.                         END IF;
  46.                     END IF;
  47.                 END IF;
  48.             END IF;
  49.         END IF;
  50.     END IF;
  51.  
  52. END $$
  53. DELIMITER ;
To copy to clipboard, switch view to plain text mode