Update tables using trigger, from a main table

Question

Update tables using trigger, from a main table

Navigation

#1 by (2 votes)
#2 by (1 votes)

2

I have the following structure:

A main table X, where the "header" of the sale is stored.

A Y table, where sales items are stored.

A Z table, where some sales data will be saved, from triggers fired from table X.

In this, every time you enter, update, or delete a record, the values in the Z-table will have to be updated by the trigger.

Follow the implemented codes:

Trigger AFTER INSERT:

DROP TRIGGER IF EXISTS depoisInsertDADF311;

DELIMITER $$
CREATE TRIGGER depoisInsertDADF311 AFTER INSERT ON dadf311 
FOR EACH ROW BEGIN

/******** ALIMENTA TABELA DADF503 ********/

DELETE FROM dadf503 WHERE anomes = NEW.anomes;

INSERT INTO dadf503(anomes, codreg, nomreg, codsup, nomsup, codven,   nomven, codpro, nompro, codcin, nomcin, valven, qtdven)
SELECT 
dadf311.anomes, dadf311.codreg, dadf311.nomreg, dadf311.codsup, dadf311.nomsup,
dadf311.codven, dadf311.nomven, dadf313.produt AS codpro, dadf313.descri AS nompro, 
dadf313.codcin, dadf313.nomcin, SUM(dadf313.valtot) AS valven, SUM(dadf313.quanti) AS qtdven
FROM 
dadf311, dadf313
WHERE
dadf311.tipnot = 'NS' AND
dadf311.anomes = dadf313.anomes AND
dadf311.filial = dadf313.filial AND
dadf311.tipnot = dadf313.tipnot AND
dadf311.numnot = dadf313.numnot AND
dadf311.sequen = dadf313.sequen  
GROUP BY anomes, codreg, codsup, codven, codpro
ORDER BY qtdven DESC;


/******** ALIMENTA TABELA DADF503 ********/


END;
$$

DELIMITER ;

Trigger AFTER DELETE:

DROP TRIGGER IF EXISTS depoisDeleteDADF311;

DELIMITER $$
CREATE TRIGGER depoisDeleteDADF311 AFTER DELETE ON dadf311 
FOR EACH ROW BEGIN
UPDATE dadf503 AS vd1, 
( SELECT dadf311.anomes, dadf311.codreg, dadf311.nomreg, dadf311.codsup,    dadf311.nomsup,
dadf311.codven, dadf311.nomven, dadf313.produt AS codpro, dadf313.descri AS nompro, 
dadf313.codcin, dadf313.nomcin, SUM(dadf313.valtot) AS valven, SUM(dadf313.quanti) AS qtdven
FROM dadf311, dadf313
WHERE dadf311.anomes = OLD.anomes AND
dadf311.anomes = dadf313.anomes AND
dadf311.filial = dadf313.filial AND
dadf311.tipnot = dadf313.tipnot AND
dadf311.numnot = dadf313.numnot AND
dadf311.sequen = dadf313.sequen AND
dadf311.tipnot = 'NS' ) AS vd2 
SET vd1.anomes = vd2.anomes, vd1.codreg = vd2.codreg, vd1.nomreg = vd2.nomreg, vd1.codsup = vd2.codsup, vd1.nomsup = vd2.nomsup,
vd1.codven = vd2.codven, vd1.nomven = vd2.nomven, vd1.codpro = vd2.codpro, vd1.nompro = vd2.nompro, vd1.codcin = vd2.codcin, 
vd1.nomcin = vd2.nomcin, vd1.valven = vd2.valven, vd1.qtdven = vd2.qtdven  
WHERE vd1.anomes = vd2.anomes; 

END;
$$
DELIMITER ;

My difficulty is in SELECT with WHERE, and if there is a way to narrow this query down to an easier form of mindset, because it visually looks "ugly."

mysql sql trigger

asked by anonymous 02.08.2018 / 20:41

2 answers

1

At first TRIGGER I organized the linking of tables in JOIN to simplify WHERE and split responsibilities. I also added WHERE to NEW.anome which indicates that only the entered record will be recalculated. As before, all records were reinserted:

DROP TRIGGER IF EXISTS depoisInsertDADF311;

DELIMITER $$
CREATE TRIGGER depoisInsertDADF311 AFTER INSERT ON dadf311
FOR EACH ROW BEGIN

/******** ALIMENTA TABELA DADF503 ********/

  DELETE FROM dadf503 WHERE anomes = NEW.anomes;

  INSERT INTO dadf503(anomes,
                      codreg,
                      nomreg,
                      codsup,
                      nomsup,
                      codven,
                      nomven,
                      codpro,
                      nompro,
                      codcin,
                      nomcin,
                      valven,
                      qtdven)
  SELECT dadf311.anomes,
         dadf311.codreg,
         dadf311.nomreg,
         dadf311.codsup,
         dadf311.nomsup,
         dadf311.codven,
         dadf311.nomven,
         dadf313.produt AS codpro,
         dadf313.descri AS nompro,
         dadf313.codcin,
         dadf313.nomcin,
         SUM(dadf313.valtot) AS valven,
         SUM(dadf313.quanti) AS qtdven
    FROM dadf311
         INNER JOIN dadf313 ON dadf311.anomes = dadf313.anomes
                           AND dadf311.filial = dadf313.filial
                           AND dadf311.tipnot = dadf313.tipnot
                           AND dadf311.numnot = dadf313.numnot
                           AND dadf311.sequen = dadf313.sequen
   WHERE dadf311.tipnot = 'NS'
     AND dadf311.anomes = NEW.anomes
   GROUP BY anomes,
            codreg,
            codsup,
            codven,
            codpro
   ORDER BY qtdven DESC;


/******** ALIMENTA TABELA DADF503 ********/


END;
$$

DELIMITER

I followed the same idea for the second TRIGGER :

DROP TRIGGER IF EXISTS depoisDeleteDADF311;

DELIMITER $$
CREATE TRIGGER depoisDeleteDADF311 AFTER DELETE ON dadf311
FOR EACH ROW BEGIN

  UPDATE dadf503 AS vd1
        (SELECT dadf311.anomes,
                dadf311.codreg,
                dadf311.nomreg,
                dadf311.codsup,
                dadf311.nomsup,
                dadf311.codven,
                dadf311.nomven,
                dadf313.produt AS codpro,
                dadf313.descri AS nompro,
                dadf313.codcin,
                dadf313.nomcin,
                SUM(dadf313.valtot) AS valven,
                SUM(dadf313.quanti) AS qtdven
           FROM dadf311
                INNER JOIN dadf313 ON dadf311.anomes = dadf313.anomes
                                  AND dadf311.filial = dadf313.filial
                                  AND dadf311.tipnot = dadf313.tipnot
                                  AND dadf311.numnot = dadf313.numnot
                                  AND dadf311.sequen = dadf313.sequen
          WHERE dadf311.anomes = OLD.anomes
            AND dadf311.tipnot = 'NS') AS vd2
      SET vd1.anomes = vd2.anomes,
          vd1.codreg = vd2.codreg,
          vd1.nomreg = vd2.nomreg,
          vd1.codsup = vd2.codsup,
          vd1.nomsup = vd2.nomsup,
          vd1.codven = vd2.codven,
          vd1.nomven = vd2.nomven,
          vd1.codpro = vd2.codpro,
          vd1.nompro = vd2.nompro,
          vd1.codcin = vd2.codcin,
          vd1.nomcin = vd2.nomcin,
          vd1.valven = vd2.valven,
          vd1.qtdven = vd2.qtdven
    WHERE vd1.anomes = vd2.anomes;

END;
$$
DELIMITER ;

I noticed that some of the fields you update do not make sense (like anome ), but it's interesting that you think if it really does not work or is right as it is.

08.08.2018 / 08:11

Unexpected behavior when centering div How do I remove the row separating the columns in an html table?

score 2 · Accepted Answer

In the case of delete you can use a subselect in where to inform the items to be deleted:

DELETE z WHERE z.Xid IN (SELECT Id FROM X)

For update you can use an " update with from ":

UPDATE z
SET z.Valor = sum(x.Valor)
FROM z z
INNER JOIN x ON x.Id = z.xID

Or even make the sum in a select and then make the update with the sum.