Trigger that accumulates the MySQL result

1

Given the following tables:

Player ( id; name number; ) j

team_ficticia ( id; userId; name; puntuacao_ficticia; ) ef

team_fiction_player ( id; teamFicticiaId; pilotId; ) efj

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

puntuacao_ficticia_player ( id; playerId; stepId; puntuacao_ficticia; ) pfj

In this scenario, several players are part of a team. Each one has a score acquired at a given stage.

I would like as soon as e.status was updated to 'f', a trigger would also be triggered that would search all the players within a certain team in the team_fiction_functions table efj and from that it would be added to the pfj.pontuacao_ficticia of each one of them accumulating these scores in effic.fictuacao_ficticia.

    
asked by anonymous 31.07.2016 / 16:13

2 answers

0

Well, from Vitor's answer I was able to solve the problem.

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 ;
    
02.08.2016 / 09:52
1

Gabriel, it will be necessary to use a CURSOR in MySQL. It is difficult to hit without testing, but your 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 ;

Triggers reference: link

Cursor reference: link

    
01.08.2016 / 13:41