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;