'2 problems when trying to create a trigger in MySQL
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!
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
