1
I have a Trigger where I have 3 cursors, but it only runs correctly one of them(soma_pointo_cursor), the other two run incorrectly, I’ve tried with three separate loops, and it didn’t work either. My perception says that it is something in relation to the Handler, he runs one of the cursors and when complete this he leaves the loop ignoring the others, I’m not sure, just an impression. I seek not only a resolution but 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 ;
What is the idea of using 3 cursors? will be to run loops inside each other?
– Marco Souza
No, run them separately. In the example they are within the same loop, because of an example I saw on the Mysql site.
– Gabriel Pereira
You who run one after the other, within the same trigger ?
– Marco Souza
Exactly that.
– Gabriel Pereira