Mysql Trigger with more than one cursor

Asked

Viewed 423 times

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?

  • No, run them separately. In the example they are within the same loop, because of an example I saw on the Mysql site.

  • You who run one after the other, within the same trigger ?

  • Exactly that.

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.