2 problems when trying to create a trigger in MySQL

Question

I am trying to understand triggers in MySQL, but am having a few problems.

I'm trying to implement a trigger which on every UPDATE/INSERT in the table Grades it updates the column "gpa" in another table called Student, but cannot do it properly.

Code:

CREATE TABLE Student 
(
    Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30),
    age TINYINT,
    gpa NUMERIC(3, 2) DEFAULT 2
);

CREATE TABLE Grades
(
    StudentId INT PRIMARY KEY,
    grade_bg INT,
    grade_math INT,
    grade_subd INT,
    FOREIGN KEY(StudentId) references Student(Id)
);

delimiter |

CREATE TRIGGER update_gpa
    AFTER INSERT
    ON Grades
    FOR EACH ROW
    BEGIN
        UPDATE Student SET gpa = ((grade_bg + grade_math + grade_subd)/3) WHERE StudentId = Id;
    END;
|

After this when I try to insert in the table Student I get: "Error 1054: Unknown column 'StudentId' in where clause".

For example:

INSERT INTO Student(name, age) 
            VALUES ('Joshua', 17);

Also when I try writing "AFTER INSERT, UPDATE" I get a syntax error from the MySQL Workbench and don't know how to make the trigger activate on INSERT AND UPDATE in the table Grades.

Any help is appreciated! Thanks in advance!

score 0 · Accepted Answer · answered Feb 11 '22 at 21:08

I don't know much about Triggers but I tried just running the update statement and noticed you hadn't defined the join:-

UPDATE Student 
INNER JOIN Grades ON Grades.StudentId = Student.Id 
SET Student.gpa = ((Grades.grade_bg + Grades.grade_math + Grades.grade_subd)/3);

2 problems when trying to create a trigger in MySQL

1 Answers1