Create Trigger between tables

Asked

Viewed 134 times

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 create two tables for logs and the first stores the device, user and the exact date that was turned on or off and the second theoretically saves the time that such device was turned on.

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 each time a log of device with the operation disable, automatically the trigger should insert into the log period information along the time period. However, for this I had to put a SELECT to find the last device activation date and with that I get syntax error in SELECT no matter how I do.

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?

java mysql query trigger

  • Welcome to Stackoverflow! I recommend you take a [tour] through our site and see [Ask].

  • No ORDER BY in select.

  • What do you mean it doesn’t exist ORDER BY in SELECT @Motta? In documentation say that where?

  • Read it in INSERT , forgiveness.

  • @Motta the ORDER BY he’s wearing in the SELECT within the INSERT, there’s no mistake in that.

1 answer

0

Apparently the error of sintaxe because it is missing to open a ( before the 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);

Browser other questions tagged java mysql query trigger

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