What is the best way to update multiple tables using a Trigger?

Question

I have a Trigger for After Insert notes After delete notes After update notes

And I need to update several tables like, sales, sales_seller.

I tested using a cursor for each table, at first it worked normally, my doubt and as to the performance and if there is another way to implement.

Follows implemented code:

/**********************************************************************************************/
DROP TRIGGER depoisInsertNotas;

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 notas.codven = NEW.codven AND DATE_FORMAT(notas.datreg, '%Y%m') = DATE_FORMAT(NEW.datreg, '%Y%m')
GROUP BY notas.codven, DATE_FORMAT(notas.datreg,'%Y%m');

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, NULL);
  INSERT INTO vendas(anomes, totven) VALUES (datan, totaln);

END LOOP;
CLOSE vendas;

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

SET done = 0;
OPEN vendas_vendedor;

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

  IF done THEN
     LEAVE done;
  END IF;

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

END LOOP;
CLOSE vendas_vendedor;
END;
$$
DELIMITER ;

Answer 1

Wribeiro,

The use of cursor is very slow, it is not recommended to use, by itself using TRIGGER is already complicated due to later maintenance, if it is not well documented it is very easy to forget and end up generating future bugs.

To resolve the situation you can use SELECT with INSERT. example:

INSERT INTO banco.tabela-destino (campo1, campo2, campo3...)
SELECT campo1,campo2,campo3... FROM banco.tabela-origem;

In your scenario it would be:

INSERT INTO vendas(anomes, totven)
SELECT DATE_FORMAT(notas.datreg, '%Y%m'), SUM(notas.totnot)
FROM notas 
WHERE DATE_FORMAT(notas.datreg, '%Y%m') = DATE_FORMAT(NEW.datreg, '%Y%m') 
GROUP BY anomes;

INSERT INTO vendas_vendedor(anomes, totven, codven, nomeven)
SELECT DATE_FORMAT(notas.datreg, '%Y%m'), SUM(notas.totnot), notas.codven, notas.nomven 
FROM notas 
WHERE notas.codven = NEW.codven AND DATE_FORMAT(notas.datreg, '%Y%m') = DATE_FORMAT(NEW.datreg, '%Y%m')
GROUP BY notas.codven, DATE_FORMAT(notas.datreg,'%Y%m');