Create database trigger from the application

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:

Code:

---

#Listing 2.
DROP TRIGGER IF EXISTS `insertPoints`; #new line
DELIMITER $$ #new line
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;
END $$ #new line

---

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:

Code:

---

QString tableResult = "results"+ctID;
    QString .tablePoint = "points"+ctID;
    QString triggerName = "insertPoints"+ctID;
 
    QString Query =  QString("DROP TRIGGER IF EXISTS "+triggerName+"; ") +
                    QString("\n") +
                    QString("DELIMITER $$  ") +
                    QString("\n") +
                    QString("CREATE TRIGGER "+triggerName+" BEFORE INSERT ON "+.tablePoint+" ") +
                    QString("FOR EACH ROW BEGIN ") +
                    QString("UPDATE "+tableResult+" SET totalPoints=totalPoints+NEW.val WHERE res_id=NEW res_id; ") +
                    QString("if NEW.val >= 10 then ") +
                    QString("UPDATE "+tableResult+" SET num_10=num_10+1 WHERE res_id=NEW.res_id; ") +
                    QString("else ") +
                    QString("if NEW.val between 9 and 10 then ") +
                    QString("UPDATE "+tableResult+" SET num_9=num_9+1 WHERE res_id=NEW.res_id; ") +
                    QString("else ") +
                    QString("if NEW.val between 8 and 9 then ") +
                    QString("UPDATE "+tableResult+" SET num_8=num_8+1 WHERE res_id=NEW.res_id; ") +
                    QString("else ") +
                    QString("if NEW.val between 7 and 8 then ") +
                    QString("UPDATE "+tableResult+" SET num_7=num_7+1 WHERE res_id=NEW.res_id; ") +
                    QString("else ") +
                    QString("if NEW.val between 6 and 7 then ") +
                    QString("UPDATE "+tableResult+" SET num_6=num_6+1 WHERE res_id=NEW.res_id; ") +
                    QString("else ") +
                    QString("if NEW.val between 5 and 6 then ") +
                    QString("UPDATE "+tableResult+" SET num_5=num_5+1 WHERE res_id=NEW.res_id; ") +
                    QString("else ") +
                    QString("if NEW.val between 4 and 5 then ") +
                    QString("UPDATE "+tableResult+" SET num_4=num_4+1 WHERE res_id=NEW.res_id; ") +
                    QString("else ") +
                    QString("if NEW.val between 3 and 4 then ") +
                    QString("UPDATE "+tableResult+" SET num_3=num_3+1 WHERE res_id=NEW.res_id; ") +
                    QString("else ") +
                    QString("if NEW.val between 2 and 3 then ") +
                    QString("UPDATE "+tableResult+" SET num_2=num_2+1 WHERE res_id=NEW.res_id; ") +
                    QString("else ") +
                    QString("if NEW.val between 1 and 2 then ") +
                    QString("UPDATE "+tableResult+" SET num_1=num_1+1 WHERE res_id=NEW.res_id; ") +
                    QString("else ") +
                    QString("if NEW.val between 0 and 1 then ") +
                    QString("UPDATE "+tableResult+" SET num_0=num_0+1 WHERE res_id=NEW.res_id; ") +
                    QString("end if; ") +
                    QString("end if; ") +
                    QString("end if; ") +
                    QString("end if; ") +
                    QString("end if; ") +
                    QString("end if; ") +
                    QString("end if; ") +
                    QString("end if; ") +
                    QString("end if; ") +
                    QString("end if; ") +
                    QString("end if; ") +
                    QString("\n") +
                    QString("END $$ ") ;

---

and execute the query :

Code:

---

QSqlQuery Query;
 Query.exec(Query);

---

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..)

Code:

---

"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"

---

Quote:

---

---------------------------------------

---

Code:

---

#Listing 1.
DROP TRIGGER IF EXISTS `insertPoints`;
DELIMITER $$
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;
 
END $$
DELIMITER ;

---

I'm responding on my thread because I resolved described problem.. Error was however in syntax.. lacked one END;

Does your points table ever have rows deleted or updated? You'll need to keep the summary table in sync.
Unless you have millions of rows I would have thought a query on demand would be more reliable.

Data won't be deleted.. Update will be reached by other trigger..
Data at the table will be counting in the hundreds so it doesn't much..

btw. thx for response..