How to create a Trigger in MySQL with more than one cursor

0

I'm trying to update two tables using the same trigger, however it only updates the sales table, but no errors occur. Here is the 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;
    
asked by anonymous 24.07.2018 / 21:43

1 answer

1

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

    
24.07.2018 / 23:06