Deciding whether a user’s new score is their best score achieved on a specific activity

I am currently trying to create a database where the user will complete a series of activities, and their score from each activity will be stored in an ‘ActivityLog’ table, here is the current format:

My actual question is when I enter a new record into this table, how would I check whether their score is their best score for that specific activity? I will need to ensure that I am checking the high score for this correct activity, as well as the correct user.

The desired output will look something similar to this (user_id and activity_id will not be null):

The improvement column is equal to true when the user’s score is greater than their highest score for that specific game, thus creating a new improvement. The logic here must only compare the highest score that this specific user has achieved on this specific activity. I am unsure whether the best approach would be using a trigger to automatically check this upon a database insert?

The reason I am storing these improvements is because I am hoping to retrieve the amount of records where the improvement is equal to true, and visualize these in a graph.

Here is the trigger I’ve got so far:

 /* Improvements Trigger */
DELIMITER //
CREATE TRIGGER checkImprovement
AFTER INSERT
ON ActivityLog
FOR EACH ROW
BEGIN
    DECLARE hs int;

    SELECT hs = MAX(score) WHERE user_id = NEW.user_id AND activity_name = NEW.activity_name;

    IF(NEW.score > hs)
       UPDATE ActivityLog
       SET improvement = 1
       WHERE log_id = NEW.log_id
    ELSE
       UPDATE ActivityLog
       SET improvement = 0
       WHERE log_id = NEW.log_id
END //
DELIMITER ;

However this code is not currently working and provides me with an error:

‘#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘WHERE user_id = NEW.user_id AND activity_name = NEW.activity_name;’

I’m sure there’s a lot to work on here, but I would appreciate a brief explanation of how to approach this!

Answer

You have a few syntax errors.

You need to use SET to assign the result of a query to a variable (or you can use SELECT ... INTO variable).

You need THEN in the IF statement, and you need ; at the end of each statement in the IF/THEN/ELSE/ block. But you don’t really need IF, since the value of a condition is either 1 or 0 depending on whether it’s true or false.

You’re missing the FROM clause in the query that gets MAX(score). I’ve used userTable below as a placeholder.

You’re updating ALL rows for all users in with the same activity, since you don’t have user_id in the WHERE clauses, nor does it specify a particular log row for the user. I think what you really want to do is set the improvement column in the new row that’s being inserted, not update existing rows. So you should use a BEFORE INSERT trigger that sets NEW.improvement.

DELIMITER //
CREATE TRIGGER checkImprovement
BEFORE INSERT ON ActivityLog
FOR EACH ROW
    SET NEW.improvement = 
        NEW.score > (
            SELECT MAX(score) 
            FROM ActivityLog
            WHERE user_id = NEW.user_id AND activity_name = NEW.activity_name);
//
DELIMITER ;

You could also combine all this into a single query:

FOR EACH ROW
    UPDATE ActivityLog SET improvement = (SELECT MAX(score)