Create a SQL trigger

user_13995464 0
Viewed 94

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)

mysql sql
1 Answers

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

user_51935360
Related