Create trigger between tables

Question

Create trigger between tables

Navigation

#1 by (0 votes)

0

I'm doing a job here and I came across a problem, our system is a home automation system using java and mysql .

We created two tables for logs , the first one saving the device, user and the exact date that was turned on or off and the second time theoretically saves the time that the device was connected.

tb_log

| Field       | Type        | Null | Key | Default | Extra          |
| id          | int(11)     | NO   | PRI | NULL    | auto_increment |    
| dispositivo | int(11)     | YES  |     | NULL    |                |    
| usuario     | int(11)     | YES  |     | NULL    |                |    
| data        | datetime    | YES  |     | NULL    |                |    
| status      | varchar(30) | YES  |     | NULL    |                |

tb_log_per

| Field       | Type     | Null | Key | Default | Extra          |
| id          | int(11)  | NO   | PRI | NULL    | auto_increment |
| dispositivo | int(11)  | YES  |     | NULL    |                |
| usuario     | int(11)  | YES  |     | NULL    |                |
| periodo     | datetime | YES  |     | NULL    |                |

The main idea was to create a trigger so that every time you enter a log of device with the deactivate operation, automatically the trigger should insert in the log of period the information next to the period of time. However, for this I had to put SELECT to find the last activation date of the device and with this I get syntax error in SELECT no matter how I do it.

CREATE TRIGGER tgr_log_insert 
AFTER INSERT ON tb_log 
FOR EACH ROW BEGIN IF( NEW.status = 'Desativado') 
THEN INSERT INTO tb_log_per (dispositivo,usuario,periodo) 
     VALUES (NEW.dispositivo,NEW.usuario,
             TIMEDIFF(SELECT data FROM tb_log WHERE dispositivo = 1 
             AND status = 'Ativo' ORDER BY data DESC LIMIT 1),NEW.data);

Does anyone know how I can perform this operation?

mysql java query trigger

asked by anonymous 15.07.2017 / 16:11

1 answer

How to mount an array with std :: array and std :: vector in C ++ 11? Get the value of a session in php

score 0 · Answer 1

Apparently, the error of sintaxe is missing because it is missing a ( before SELECT :

CREATE TRIGGER tgr_log_insert AFTER INSERT ON tb_log FOR EACH ROW
    BEGIN
        IF( NEW.status = 'Desativado') THEN
            INSERT INTO tb_log_per (dispositivo, usuario, periodo) VALUES (NEW.dispositivo, NEW.usuario, TIMEDIFF((
                SELECT data FROM tb_log
                WHERE dispositivo = 1 AND status = 'Ativo'
                ORDER BY data DESC
                LIMIT 1),
            NEW.data);