Helloo !
So I am a bit noob in SQL and I'm trying to create a trigger to get the average score of a movie.
I got three tables, users, relation and movies :
In users there is an id, name, pwd, etc.
In movies there is an id, title, director and averageScore (that I
want to update)
In relation there is the user ID called idUser, the movie ID called
idMovie and the score
So each time that a user adds a movie, there is a new relation row with the id of the User, the id of the movie and the score that the user gives
I want to make an average of the score for each movie each time that a new relation is created
So for that I create this trigger
CREATE TRIGGER Average
AFTER INSERT ON relation
FOR EACH ROW
UPDATE movies
SET averageScore = (SELECT AVG(score) FROM relation WHERE movies.id = relation.idMovie)
But when a new relation is created, the averageScore of movies doesn't change, and I can't find the problem. Please help me ! x)
The trigger was a good start, but you missed some points.
movies.id is not part of relations the new values you adress with NEW.idMovie
The last is you have to specify the row you want to update.
CREATE TABLE movies(id int, averageScore DECIMAL(4,2)); CREATE TABLE relation(iduser INT, idMovie INT,score DECIMAL(4,2));
INSERT INTO movies VALUES (1,0),(2,0);
CREATE TRIGGER Average AFTER INSERT ON relation FOR EACH ROW UPDATE movies SET averageScore = (SELECT AVG(score) FROM relation WHERE idMovie = NEW.idMovie) WHERE movies.id = NEW.idMovie
INSERT INTO relation VALUES (1,1,0.5),(2,1,0.7),(3,1,0.2)
SELECT * FROM movies;id | averageScore -: | -----------: 1 | 0.47 2 | 0.00
db<>fiddle here