SQL: set trigger to get the sum of rows-CodePudding

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

CodePudding user response：

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