Set Insert trigger to store max value in another column

user_16573885 0
Viewed 30

This is what my table looks like:

student_id subject_id total max
101 1 6
102 2 5
103 1 9
101 1 10
103 2 2
104 1 7

I want the "max" column to be automatically populated when the total is inserted.

Expected Output:

student_id subject_id total max
101 1 10 10
102 2 5 7
103 1 9 10
101 1 8 10
103 2 2 7
104 1 7 10

I will like to create a trigger for this.

This is my SELECT statement which works fine but how do I put it in a trigger?

WITH CTE AS (SELECT `subject_id`,MAX(`total`) AS MaxTotal
FROM results
GROUP BY `subject_id`
)
SELECT results.*,CTE.MaxTotal
FROM results
JOIN CTE ON results.`subject_id` = CTE.`subject_id`;

I did this but I got a plethora of errors

CREATE TRIGGER `max_score_before_INSERT` BEFORE INSERT ON `results`
FOR EACH ROW 
SET NEW.max = (WITH CTE AS (SELECT `subject_id`,MAX(`NEW.total`) AS MaxTotal
FROM results
GROUP BY `subject_id`
)
SELECT results.*,CTE.MaxTotal
FROM results
JOIN CTE ON results.`subject_id` = CTE.`subject_id`
);
mariadb phpmyadmin sql
1 Answers

You can't reference "new" inside a BEFORE trigger type. You may want to first insert the row, then update the column with an AFTER trigger type:

CREATE TRIGGER `max_score_before_INSERT` AFTER INSERT ON `results`
FOR EACH ROW 
UPDATE     <your_table_name>
INNER JOIN (SELECT subject_id, 
                   MAX(total) AS total_max
            FROM <your_table_name>
            GROUP BY subject_id) cte
        ON <your_table_name>.subject_id = cte.subject_id
SET <your_table_name>.max = cte.total_max;

Note that this approach will update the previous columns too, as if you insert a new field that becomes the max, you may want to update the already existing rows too. If not, then you can use a condition inside the UPDATE statement:

CREATE TRIGGER `max_score_before_INSERT` AFTER INSERT ON `results`
FOR EACH ROW 
UPDATE     <your_table_name>
INNER JOIN (SELECT subject_id, 
                   MAX(total) AS total_max
            FROM <your_table_name>
            GROUP BY subject_id) cte
        ON <your_table_name>.subject_id = cte.subject_id
SET <your_table_name>.max = cte.total_max;
WHERE <your_table_name>.student_id = NEW.student_id
  AND <your_table_name>.subject_id = NEW.subject_id;
user_124928900
Related