Trigger with sum between dates

Question

Trigger with sum between dates

Navigation

#1 by (1 votes)

0

Good Night, I need help that I can not get, I have two tables in my db.

1 - Portfolio table composed of:

CREATE TABLE 'carteira' (
  'id' int(11) NOT NULL,
  'datat' datetime DEFAULT NULL,
  'vl_cliente' decimal(10,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO 'carteira' ('id', 'datat', 'vl_cliente') VALUES
(1, '2018-05-01 06:19:18', '50.00'),
(2, '2018-05-01 11:05:15', '100.00'),
(3, '2018-05-02 02:24:07', '20.00'),
(4, '2018-05-02 03:11:00', '80.00'),
(5, '2018-05-02 10:29:43', '1.00'),
(6, '2018-05-03 08:27:42', '500.00'),
(7, '2018-05-04 02:16:20', '100.00'),
(8, '2018-05-04 05:19:29', '3.50'),
(9, '2018-05-05 08:21:07', '10.00'),
(10, '2018-05-06 08:27:39', '123.00'),
(11, '2018-05-06 10:30:26', '21.00'),
(12, '2018-05-06 10:32:00', '800.00'),
(13, '2018-05-06 11:16:43', '900.00'),
(14, '2018-05-06 15:29:16', '50.00'),
(15, '2018-05-05 18:15:00', '80.00');

2 - Consolidated Table consisting of:

CREATE TABLE 'consolidado' (
  'id' int(11) NOT NULL,
  'datat' date DEFAULT NULL,
  'vl_cliente' decimal(10,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO 'consolidado' ('id', 'datat', 'vl_cliente') VALUES
(1, '2018-05-01', NULL),
(2, '2018-05-02', NULL),
(3, '2018-05-03', NULL),
(4, '2018-05-04', NULL),
(5, '2018-05-05', NULL),
(6, '2018-05-06', NULL);

I'm trying to do a trigger where every time INSERT, UPDATE and DELETE

Add the value and INSERT or UPDATE in the consolidated table, plus the problem is that in addition to the sum you have to do the formatting and grouping by date

Ex. of final result:

2018-05-01 150,00
2018-05-02 101,00
2018-05-03 500,00
2018-05-04 103,50
2018-05-05 90,00
2018-05-06 1894,0

I'm stopped with this trigger:

DELIMITER $$
CREATE TRIGGER 'consolida' AFTER INSERT ON 'carteira' FOR EACH ROW UPDATE consolidado a
   SET a.vl_cliente = 
    (SELECT SUM(vl_cliente) 
       FROM carteira
      WHERE datat = a.datat)
 WHERE a.datat = NEW.date_format(datat, '%Y-%m-%d')
$$
DELIMITER ;

I need some help to solve this problem, thanks in advance

php mysql sql phpmyadmin trigger

asked by anonymous 12.05.2018 / 01:01

1 answer

Problem in deallocation of memory Error {"The object reference was not defined as an instance of an object."} System.NullReferenceException MVC ASP.NET

score 1 · Accepted Answer

The table of consolidated values can use the date of consolidation of the values as its primary key, see:

CREATE TABLE tbl_carteira
(
  id BIGINT PRIMARY KEY,
  datat DATETIME,
  vl_cliente NUMERIC(10,2)
);

CREATE TABLE tbl_consolidado
(
  datat DATE PRIMARY KEY,
  vl_total DECIMAL(10,2)
);

Unfortunately, in MySQL you need to create a specific trigger for each type of operation of INSERT , UPDATE and DELETE . To avoid replication of the same code in these triggers , the tip is to create a unique stored procedure that would be called by all those triggers . > The stored procedure needs to be able to identify whether the consolidated value record already exists, this will determine whether the record should be included or just updated:

CREATE PROCEDURE fc_consolidar_carteira( fdatat DATE )
BEGIN
  IF (SELECT 1 = 1 FROM tbl_consolidado WHERE datat = fdatat ) THEN
  BEGIN
    UPDATE
        tbl_consolidado
    SET
      vl_total = (SELECT sum(c.vl_cliente)
                  FROM tbl_carteira AS c
                  WHERE CAST( c.datat AS DATE ) = fdatat )
    WHERE
        datat = fdatat;
  END;
  ELSE
  BEGIN
    INSERT INTO tbl_consolidado ( datat, vl_total ) ( SELECT fdatat, c.vl_cliente
                                                      FROM tbl_carteira AS c
                                                      WHERE CAST( c.datat AS DATE ) = fdatat);
  END;
  END IF;
END;

And triggers of INSERT , DELETE and UPDATE , respectively:

CREATE TRIGGER trg_ai_consolida AFTER INSERT ON tbl_carteira FOR EACH ROW
BEGIN
  CALL fc_consolidar_carteira( CAST( NEW.datat AS DATE ) );
END;

CREATE TRIGGER trg_ad_consolida AFTER DELETE ON tbl_carteira FOR EACH ROW
BEGIN
  CALL fc_consolidar_carteira( CAST( OLD.datat AS DATE ) );
END;

CREATE TRIGGER trg_au_consolida AFTER UPDATE ON tbl_carteira FOR EACH ROW
BEGIN
  CALL fc_consolidar_carteira( CAST( NEW.datat AS DATE ) );
END;

SQLFiddle: link