How to create a trigger in MySQL?

Question

How to create a trigger in MySQL?

Navigation

#1 by (5 votes)
#2 by (2 votes)

5

I have tables usuarios , usuario_voto , and classificacao_usuario .

Usuarios: system user table.

usuario_voto: table that stores vote from one user to another.

classificacao_usuario: table that stores average votes for a user.

Running.

Each user has a user-class table, where his average number of votes is listed.

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

Goal

When a user votes on another (insert usuario_voto ), I want a trigger to be triggered where after voting, the column note of the classificao_usuario table is updated. The trigger must get the average vote for this user who was voted in the usuario_voto table.

I think the trigger select should look 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 would have been more like this

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

mysql trigger

asked by anonymous 22.04.2015 / 08:34

2 answers

Do I need to include jQuery on all pages? What exactly is the element.class selector?

score 5 · Answer 1

To do this you have to create a Trigger that is activated after a INSERT is made in the usuario_voto table.

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 only runs only if the INSERT runs correctly. If an error occurs during INSERT execution, the trigger will not be executed.

score 2 · 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 votes of this user and then do the update.