Trigger with sum between dates

Question

Good evening, I need a help I’m not getting, 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 composed 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 make a Trigger where every time he went INSERT, UPDATE & DELETE.

Add the value and INSERT or UPDATE in the consolidated table, but the problem is that besides the sum has to be done to 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 stuck with this Rigger:

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, I thank you in advance

Answer 1

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

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 the MySQL you need to create a Trigger specific to each type of INSERT, UPDATE and DELETE. To avoid replication of the same code in these triggers, the tip is to create a stored Procedure only that would be called by all these triggers.

To stored Procedure need to be able to identify if the consolidated value record already exists, it will determine whether the record should be included or only 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 the 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: http://sqlfiddle.com/#! 9/a7fc4e/1