How to create a Mysql Trigger with more than one cursor

Question

I’m trying to update two tables using the same Rigger, but it only updates the sales table, but no errors occur. Follows code:

DELIMITER $$  
CREATE TRIGGER depoisInsertNotas AFTER INSERT ON notas
FOR EACH ROW 
BEGIN
   DECLARE done BOOLEAN DEFAULT FALSE;

   DECLARE datan INT DEFAULT 0;
   DECLARE totaln DECIMAL(18,4);

   DECLARE codven INT;
   DECLARE nomeven VARCHAR(150);

   DECLARE vendas CURSOR FOR 
   SELECT DATE_FORMAT(notas.datreg, '%Y%m') AS anomes, SUM(notas.totnot) AS total 
   FROM notas 
   WHERE DATE_FORMAT(notas.datreg, '%Y%m') = DATE_FORMAT(NEW.datreg, '%Y%m') 
   GROUP BY anomes;

   DECLARE vendas_vendedor CURSOR FOR 
   SELECT DATE_FORMAT(notas.datreg, '%Y%m') AS anomes, SUM(notas.totnot) AS total, notas.codven, notas.nomven 
   FROM notas 
   WHERE DATE_FORMAT(notas.datreg, '%Y%m') = DATE_FORMAT(NEW.datreg, '%Y%m') 
   GROUP BY codven;

   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE;

   OPEN vendas;

   done: LOOP 
      FETCH vendas INTO datan, totaln;

      IF done THEN
         LEAVE done;
      END IF;

      CALL deletaNotas(datan);
      INSERT INTO vendas(anomes, totven) VALUES (datan, totaln);

   END LOOP;
   CLOSE vendas;

   /*********************************************/

   OPEN vendas_vendedor;

      done: LOOP 
         FETCH vendas_vendedor INTO datan, totaln, codven, nomeven;

            IF done THEN
               LEAVE done;
            END IF;

            CALL deletaNotas(datan);
            INSERT INTO vendas_vendedor(anomes, totven, codven, nomeven) VALUES (datan, totaln, codven, nomeven);

      END LOOP;
   CLOSE vendas_vendedor;
END$$
DELIMITER;

Answer 1

Try to reset the done before opening the second cursor SET done=0;.