Results 1 to 4 of 4

Thread: Create database trigger from the application

  1. #1
    Join Date
    Dec 2013
    Posts
    5
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11

    Default 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:

    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 

  2. #2
    Join Date
    Dec 2013
    Posts
    5
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11

    Default Re: Create database trigger from the application

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

  3. #3
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,641
    Thanks
    13
    Thanked 1,592 Times in 1,520 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: Create database trigger from the application

    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.

  4. #4
    Join Date
    Dec 2013
    Posts
    5
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11

    Default Re: Create database trigger from the application

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

Similar Threads

  1. Replies: 0
    Last Post: 15th October 2013, 09:32
  2. create database if not exist
    By snaderi in forum Qt Programming
    Replies: 4
    Last Post: 29th June 2013, 22:33
  3. how to send sqlite query CREATE TRIGGER in qt application
    By abdul_moiz in forum Qt Programming
    Replies: 2
    Last Post: 10th June 2011, 06:07
  4. Create database
    By waynew in forum Newbie
    Replies: 2
    Last Post: 8th October 2009, 12:10
  5. Run database application from Konqueror
    By jiveaxe in forum Newbie
    Replies: 3
    Last Post: 17th October 2007, 17:54

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.