-- https://www.phpmyadmin.net/
--
-- Tempo de geração: 15-Jun-2023 às 20:29
-- Versão do servidor: 10.4.24-MariaDB
-- versão do PHP: 8.1.6

START TRANSACTION;



--
-- Banco de dados: `a`
--

-- --------------------------------------------------------

--
-- Estrutura da tabela `leituradaju`
--

CREATE TABLE `leituradaju` (
  `idLeituraDaJu` int(10) UNSIGNED NOT NULL,
  `Sensor_idSensor` int(10) UNSIGNED NOT NULL,
  `Mac_idMac` varchar(17) COLLATE utf8mb4_bin NOT NULL,
  `DataLeitura` date DEFAULT NULL,
  `HoraLeitura` text COLLATE utf8mb4_bin DEFAULT NULL,
  `ValorSensor` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

--
-- Extraindo dados da tabela `leituradaju`
--

INSERT INTO `leituradaju` (`idLeituraDaJu`, `Sensor_idSensor`, `Mac_idMac`, `DataLeitura`, `HoraLeitura`, `ValorSensor`) VALUES
(1, 1, 'CC:50:E3:3B:FE:E9', '2023-06-15', '2:33:58', 16),
(2, 1, 'CC:50:E3:3B:FE:E9', '2023-06-15', '2:40:13', 18),
(3, 1, 'CC:50:E3:3B:FE:E9', '2023-06-15', '2:40:15', 10),
(4, 1, 'CC:50:E3:3B:FE:E9', '2023-06-15', '2:40:17', 10),
(5, 1, 'CC:50:E3:3B:FE:E9', '2023-06-15', '2:43:19', 16),
(6, 1, 'CC:50:E3:3B:FE:E9', '2023-06-15', '2:43:23', 10),
(7, 1, 'CC:50:E3:3B:FE:E9', '2023-06-15', '2:50:54', 8),
(8, 1, 'CC:50:E3:3B:FE:E9', '2023-06-15', '2:52:08', 15),
(9, 1, 'CC:50:E3:3B:FE:E9', '2023-06-15', '2:52:10', 8),
(10, 1, 'CC:50:E3:3B:FE:E9', '2023-06-15', '2:54:12', 15),
(11, 1, 'CC:50:E3:3B:FE:E9', '2023-06-15', '2:54:14', 15),
(12, 1, 'CC:50:E3:3B:FE:E9', '2023-06-15', '2:54:18', 8),
(13, 1, 'CC:50:E3:3B:FE:E9', '2023-06-15', '2:54:19', 8),
(14, 1, 'CC:50:E3:3B:FE:E9', '2023-06-15', '2:54:24', 14),
(15, 1, 'CC:50:E3:3B:FE:E9', '2023-06-15', '2:54:57', 8),
(16, 1, 'CC:50:E3:3B:FE:E9', '2023-06-15', '3:00:45', 15),
(17, 1, 'CC:50:E3:3B:FE:E9', '2023-06-15', '3:06:05', 15),
(18, 1, 'CC:50:E3:3B:FE:E9', '2023-06-15', '3:13:51', 8),
(19, 1, 'CC:50:E3:3B:FE:E9', '2023-06-15', '3:13:55', 15);

--
-- Acionadores `leituradaju`
--
DELIMITER $$
CREATE TRIGGER `TGDeleteDaJu` AFTER DELETE ON `leituradaju` FOR EACH ROW BEGIN
UPDATE mac
SET Contador=Contador-1
WHERE idMac=OLD.mac_idMac;
UPDATE sensor
SET Contador=Contador-1
WHERE idsensor=OLD.sensor_idsensor;
END
$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER `TGInsertDaJu` AFTER INSERT ON `leituradaju` FOR EACH ROW BEGIN
UPDATE Mac
SET Contador=Contador+1
WHERE idMac=NEW.mac_idMac;
UPDATE Sensor
SET Contador=Contador+1
WHERE idSensor=NEW.sensor_idSensor;
END
$$
DELIMITER ;

--
-- Índices para tabelas despejadas
--

--
-- Índices para tabela `leituradaju`
--
ALTER TABLE `leituradaju`
  ADD PRIMARY KEY (`idLeituraDaJu`);
COMMIT;

