Update tables using Trigger from a main table

Asked

Viewed 269 times

2

I have the following structure:

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

A Y table, where the sale items are stored.

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

In this, every time a record is inserted, updated, or deleted, the values of table Z will have to be updated by Trigger.

Follows 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 decrease this query to an easier way of understanding, because visually it looks "ugly".

mysql sql database trigger

  • It was not enough to put in your question an important aspect, which is your problem today and what difficulties you are experiencing to implement the solution you want!

  • Oops, I edited the question

2 answers

2


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

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

In the case of update you can use a "update com 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 into one select and then do the update with the sum.

  • Can you give an example on top of my querys? I’m still a little confused rs, because so, every time I change some data in the main table, it needs to delete the record from the secondary table, then update with the updated sum value, in the SELECT SUM().

  • create the bd schema and pass me the link, you can use this site http://sqlfiddle.com, then mount the update for Voce, why then I mount and send to Voce without error

  • I created the schema here, https://www.db-fiddle.com/f/jhBXw3cgCRq53eArMnc1yK/0 .

1

At first TRIGGER I organized the link of the tables in JOIN to simplify the WHERE and share responsibilities. I also added WHERE to the NEW.anome which indicates that only the entered record will be recalculated. As it was 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 fields you update do not make sense (such as anome) but it’s interesting that you think if you don’t really do it or if you’re right as you are.

  • has some difference in using JOIN or WHERE?

  • @write semantically yes. You can check this out [na[(https://answall.com/questions/6441/qual%C3%A9-a-difference%C3%A7a-entre-Inner-Join-e-outer-Join) question.

  • beauty, I think I get it!

Browser other questions tagged mysql sql database trigger

You are not signed in. Login or sign up in order to post.