Trigger that accumulates the Mysql result

Question

Considering the following tables:

Gambler( id; name number. ) j

equipe_ficticia( id; user name; name; dot_fictitious; ) Ef

equipe_ficticia_player( id; equipeFicticiaId; pilotoId; ) efj

step( id; local; year; state; ) and

dot_fictiti_player( id; player; etapaId; dot_fictitious; ) pfj

In this scenario, several players are part of a team. Each has a score earned in a given stage.

I’d like it to be upgraded to 'f', a Trigger was also fired to search for all players within a given team in the table team team team team team efj and from that it was added to pfj.fictional score_score of each of them accumulating these scores in fictitious Ef.score_score.

Answer 1

Gabriel, it will be necessary to use a CURSOR in Mysql. It’s hard to get it right without testing, but Trigger would look something like this:

DELIMITER //
CREATE TRIGGER upd_etapa AFTER UPDATE ON etapa
FOR EACH ROW
BEGIN
    DECLARE pfj_equipe_ficticia_id INTEGER;
    DECLARE pfj_pontuacao_ficticia_total INTEGER;

    DECLARE pfj_cursor CURSOR FOR
        SELECT efj.equipe_ficticia_id, SUM(pfj.pontuacao_ficticia)
        FROM pontuacao_ficticia_jogador pfj
        INNER JOIN equipe_ficticia_jogador efj ON efj.jogador_id = pfj.jogador_id
        WHERE pfj.etapa_id = NEW.id
        FOR UPDATE;

    DECLARE pfj_cursor_done INTEGER DEFAULT 0;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET pfj_cursor_done = 1;

    IF NEW.estado = 'f' THEN
    BEGIN
        OPEN pfj_cursor;

        pfj_cursor_loop: LOOP
            FETCH pfj_cursor INTO pfj_equipe_ficticia_id, pfj_pontuacao_ficticia_total;
            IF pfj_cursor_done = 1 THEN
                LEAVE pfj_cursor_loop;
            END IF;

            UPDATE equipe_ficticia SET pontuacao_ficticia = pfj_pontuacao_ficticia_total WHERE id = pfj_equipe_ficticia_id; 
        END LOOP pfj_cursor_loop;
        CLOSE pfj_cursor;
    END IF;
END;//
DELIMITER ;

Reference of triggers (triggers): https://dev.mysql.com/doc/refman/5.5/en/trigger-syntax.html

Cursor reference: http://dev.mysql.com/doc/refman/5.7/en/cursors.html

Answer 2

Well, from Vitor’s answer I was able to solve the question.

DELIMITER //
    CREATE TRIGGER upd_etapa AFTER UPDATE ON etapa
    FOR EACH ROW
    BEGIN
       DECLARE pfj_equipe_ficticia_id INTEGER;
       -- DECLARE pfj_pontuacao_ficticia_total INTEGER;

       DECLARE pfj_cursor CURSOR FOR
          SELECT efp.equipeId
          FROM equipe_ficticia ef
          INNER JOIN equipe_ficticia_jogador efj ON efj.equipeId = ef.id
          INNER JOIN jogador j ON efj.jogadorId=j.id
          INNER JOIN pontuacao_ficticia_jogador pfj ON j.id=pfj.jogadorId
          WHERE pfj.etapaId = NEW.id GROUP BY ef.id
    FOR UPDATE;
       FOR UPDATE;

       DECLARE pfj_cursor_done INTEGER DEFAULT 0;

       DECLARE CONTINUE HANDLER FOR NOT FOUND SET pfj_cursor_done = 1;

       IF NEW.estado = 'f' THEN
          BEGIN
          OPEN pfj_cursor;

          pfj_cursor_loop: LOOP
          FETCH pfj_cursor INTO pfj_equipe_ficticia_id;
          IF pfj_cursor_done = 1 THEN
            LEAVE pfj_cursor_loop;
          END IF;

          SELECT SUM(pfj.pontuacao_ficticia) into @a FROM pontuacao_ficticia_jogador pfj
          INNER JOIN jogador j ON pfj.jogadorId=j.id
          INNER JOIN equipe_ficticia_jogador efj ON j.id=efj.jogadorId
          WHERE pfj.etapaId=NEW.id AND efj.equipeId=pfp_equipe_ficticia_id;


         UPDATE equipe_ficticia          
         SET pontuacao_ficticia = @a WHERE id = pfp_equipe_ficticia_id; 
         END LOOP pfj_cursor_loop;
         CLOSE pfj_cursor;
         END
       END IF;
    END;//
   DELIMITER ;