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("CREATE TRIGGER "+triggerName
+" BEFORE INSERT ON "+.
tablePoint+" ") + 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("if NEW.val between 9 and 10 then ") + QString("UPDATE "+tableResult
+" SET num_9=num_9+1 WHERE res_id=NEW.res_id; ") + 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("if NEW.val between 7 and 8 then ") + QString("UPDATE "+tableResult
+" SET num_7=num_7+1 WHERE res_id=NEW.res_id; ") + 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("if NEW.val between 5 and 6 then ") + QString("UPDATE "+tableResult
+" SET num_5=num_5+1 WHERE res_id=NEW.res_id; ") + 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("if NEW.val between 3 and 4 then ") + QString("UPDATE "+tableResult
+" SET num_3=num_3+1 WHERE res_id=NEW.res_id; ") + 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("if NEW.val between 1 and 2 then ") + QString("UPDATE "+tableResult
+" SET num_1=num_1+1 WHERE res_id=NEW.res_id; ") + 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 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 :
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
Bookmarks