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

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

and execute the query :


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


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


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

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

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