How to create a trigger in Mysql?

Question

I have the tables usuarios, usuario_voto, and classificacao_usuario.

Usuarios: system users table.

usuario_voto: table that stores a user’s vote on another.

classificacao_usuario: table that stores a user’s average of votes.

Functioning.

Each user has a table of classificao_usuario, with their average of votes.

The table usuario_voto has the columns usuario_votante, usuario_votado and nota .

Goal

When a user votes for another user(insert usuario_voto), I want to trigger a trigger where after the vote, is updated the table note column classificao_usuario. Trigger should take the average vote of this user that was voted on the table usuario_voto) .

I think the trigger select should stay more like this.

SELECT AVG(nota) FROM usuario_voto as v WHERE v.usuario_votado = <ID_DO_USUARIO_QUE_QUERO> group by v.usuario_votado

and the update I think will look more like this

UPDATE classificacao_usuario SET nota = <MEDIA> WHERE  id = <ID_DO_USUARIO_QUE_QUERO>

Answer 1

To do this you need to create a Trigger that is activated after a INSERT on the table usuario_voto.

Example of a Trigger in Mysql:

CREATE TRIGGER ActualizaDados AFTER INSERT ON usuario_voto
  FOR EACH ROW
  BEGIN
    -- Aqui dentro faz o UPDATE para actualizar os dados.
    UPDATE classificacao_usuario SET nota = <MEDIA> WHERE  id = <ID_DO_USUARIO_QUE_QUERO>
  END;

Note: This Trigger is only executed if INSERT runs correctly. If an error occurs during INSERT execution Trigger will not be executed.

Answer 2

delimiter //
CREATE TRIGGER nomedoTrigger after INSERT ON usuario_voto
FOR EACH ROW
BEGIN
Aqui você coloca a estrutura do trigger.
END //
delimiter ;

I think you should create a variable that takes the value of the average vote of that user and then update.