Mysql Trigger with more than one cursor

1

I have a Trigger where I have 3 cursors, but it only correctly executes one of them (soma_pontuacao_cursor), the other two are executed in the wrong way, I already tried with three separate loops, and it also did not work. My perception says that it is something related to the Handler, it executes one of the cursors and when it completes this it leaves the loop ignoring the others, anyway I'm not sure, just an impression. I look beyond a resolution for an explanation. Follow the trigger:

DELIMITER $$
CREATE TRIGGER upd_pontuacao_equipes AFTER UPDATE ON grande_premio
FOR EACH ROW
BEGIN
DECLARE pfp_equipe_ficticia_id INTEGER;
DECLARE pfp_cursor_done INTEGER DEFAULT 0;
DECLARE piloto_Id INTEGER DEFAULT 0;    
DECLARE piloto_Id1 INTEGER DEFAULT 0;   

DECLARE equipe_Id INTEGER DEFAULT 0; 
DECLARE soma_pontuacao_cursor CURSOR FOR
    SELECT c.pilotoId FROM corrida c
    WHERE c.gpId=NEW.id;
DECLARE soma_posicoes_cursor CURSOR FOR
    SELECT er.id,c.pilotoId FROM corrida c
    INNER JOIN pilotos p ON c.pilotoId=p.id
    INNER JOIN equipes_reais er ON p.equipeId=er.id;
DECLARE pfp_cursor CURSOR FOR
    SELECT efp.equipeId
    FROM equipe_ficticia ef
    INNER JOIN equipe_ficticia_pilotos efp ON efp.equipeId = ef.id
    INNER JOIN pilotos p ON efp.pilotoId=p.id
    INNER JOIN pontuacao_ficticia_piloto pfp ON p.id=pfp.pilotoId
    WHERE pfp.gpId = NEW.id GROUP BY ef.id
    FOR UPDATE;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET pfp_cursor_done = 1;

IF NEW.ativo = 'c' THEN
BEGIN
    OPEN pfp_cursor;
    OPEN soma_pontuacao_cursor;
    OPEN soma_posicoes_cursor;              
    -- cursor 1
    soma_posicoes_cursor_loop:LOOP
    FETCH soma_posicoes_cursor INTO equipe_Id,piloto_Id1;
    FETCH soma_pontuacao_cursor INTO piloto_Id;     
    FETCH pfp_cursor INTO pfp_equipe_ficticia_id;
    IF pfp_cursor_done = 1 THEN
            LEAVE soma_posicoes_cursor_loop;
        END IF;

    SELECT SUM(posicao) INTO @sp FROM corrida WHERE pilotoId=piloto_Id1 AND gpId=NEW.id;
    SELECT SUM(posicoes) INTO @p FROM equipes_reais WHERE id=equipe_Id;

    UPDATE equipes_reais SET posicoes=@sp+@p WHERE id=equipe_Id;    

    -- cursor 2        

    SELECT SUM(pfp.pontuacao_ficticia) into @a FROM pontuacao_ficticia_piloto pfp
    INNER JOIN pilotos p ON pfp.pilotoId=p.id
    INNER JOIN equipe_ficticia_pilotos efp ON p.id=efp.pilotoId
    WHERE pfp.gpId=NEW.id AND efp.equipeId=pfp_equipe_ficticia_id;

    UPDATE equipe_ficticia          
    SET pontuacao_ficticia = @a WHERE id = pfp_equipe_ficticia_id; 
    -- cursor 3
    SELECT SUM(pontuacao_ficticia) INTO @c FROM corrida WHERE pilotoId=piloto_Id AND gpId=NEW.id;
    SELECT SUM(pontuacao_ficticia) INTO @q FROM qualify WHERE pilotoId=piloto_Id AND gpId=NEW.id;

     UPDATE pontuacao_ficticia_piloto          
        SET pontuacao_ficticia = @c+@q WHERE pilotoId=piloto_Id AND gpId=NEW.id;
    END LOOP;     
    CLOSE soma_posicoes_cursor;
    CLOSE pfp_cursor;
    CLOSE soma_pontuacao_cursor;

END;
END IF;

END; $$
DELIMITER ;
    
asked by anonymous 03.08.2016 / 10:52

0 answers