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 ;