Create database trigger from the application

[mikajlo]

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:

#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

#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

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:

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 $$ ") ;

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 $$ ") ;

To copy to clipboard, switch view to plain text mode 

and execute the query :

QSqlQuery Query;
 Query.exec(Query);

QSqlQuery Query;
 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..)

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

"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 

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

#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 ;

#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 ;

To copy to clipboard, switch view to plain text mode 

[mikajlo]

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

[ChrisW67]

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.

[mikajlo]

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