CODEWITHSUNDEEP
mysqlsql
Eviane
Eviane

Reputation: 33

Create a SQL trigger

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 :

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)

Upvotes: 2

Views: 131

Answers (1)

nbk
nbk

Reputation: 49373

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

Upvotes: 2

Related Questions