SQL: set trigger to get the sum of rows

June 20, 2022

This is the results table:

student_id | subject_id  | get_ca1  | get_ca2  | get_exam | get_total
----------------------------------------------------------------------
   101     |     1       | 10       | 7        | 10       |
   102     |     2       | 5        | 5        | 10       |
   103     |     1       | 9        | 10       | 4        |
   101     |     1       | 8        | 10       | 10       |
   103     |     2       | 2        | 10       | 10       |
   104     |     1       | 7        | 8        | 5        |
   101     |     2       | 7        | 8        | 5        |

I want to create a trigger that will sum up the rows get_ca1 + get_ca2 ...+ get_exam and store the total in the get_total column.
When the rows get_ca1, get_ca2,... get_exam are inserted, the trigger should calculate the total and store in the get_total column.

I just learnt about triggers today so I’m not knowledgeable in it at all. But so far, this is what I tried and it of course threw an error.

CREATE TRIGGER `sum total ` AFTER INSERT ON `exam_group_exam_results`
FOR EACH ROW SET get_tot_score = (get_ca1 + get_ca2 + get_ca3 + get_ca4 + get_ca5 + get_ca6 + get_exam);

>Solution :

Mysql doesn’t know where your columns belog because you are missing a reference to the NEW row.

Also you can only change values, before the row was inserted

so use

CREATE TRIGGER `sum_total_before_INSERT` BEFORE INSERT ON `exam_group_exam_results`
FOR EACH ROW 
SET NEW.get_tot_score = (NEW.get_ca1 + NEW.get_ca2 + NEW.get_ca3 + NEW.get_ca4 + NEW.get_ca5 + NEW.get_ca6 + NEW.get_exam);