Trigger with MySQL query


What I need to do is the following, when a record is entered into the orders table, a trigger is made to perform the following query:

 SELECT timestampdiff(MINUTE, fechaDeSolicitud, now()) <= 120 FROM pedidos
 WHERE idPedido= (aqui iria el id del pedido que ingreso como registro)

But I need to repeat it until the result of this 0 query

(Will return 1 if 120 minutes have not yet passed or 0 otherwise)

Any idea how I can do it that way, not with a trigger? thank you very much.

asked by Lina Cortés 23.05.2016 в 04:44

4 answers


I would recommend doing an event every so often, there are options to refresh automatically in javascript or use a button to update manually. and call a function that returns the answer of 0 or 1.

CREATE DEFINER='root'@'' FUNCTION 'menor_a_120'(fechaDeSolicitud datetime) RETURNS int(11)
    SET @diferencia_minutos=(SELECT timestampdiff(MINUTE, fechaDeSolicitud, now()) );
    IF (@diferencia_minutos<=120) THEN
        SET valor=0;
        SET valor=1;
    END IF;
RETURN valor;

and then in your javascript you send a query and see what it gives back, so you do not load so much work into the database.

answered by 23.05.2016 в 16:12

You can try something like that, although I notice that I have not tried it:

CREATE TRIGGER trigger_pedidos AFTER INSERT ON pedidos
        WHILE v1 > 0 DO
          SET v1 = (SELECT timestampdiff(MINUTE, fechaDeSolicitud, now()) <= 120 FROM pedidos
                    WHERE idPedido= (aqui iria el id del pedido que ingreso como registro))
        END WHILE;
answered by 23.05.2016 в 06:27

According to what I understand, the consultation could be 120 minutes waiting for the condition. The best thing if you are using linux is to do a process that does that work and that is executed for example every 1 minute, you enter it in crontab


answered by 23.05.2016 в 06:44

I do not see much point in creating a trigger that executes a query, unless the result of that query is saved in a temporary table or in a variable that you can use in another part of your project .

If you want to do something like that, I can think of this:

-- Opcion 1: Almacenar el resultado en una variable
delimiter $$
create trigger tr_pedidos_ai 
after insert on pedidos
for each row
    SELECT timestampdiff(MINUTE, fechaDeSolicitud, now()) <= 120 
        INTO @ts_diff
    FROM pedidos
    WHERE idPedido=new.id_pedido;
end $$
delimiter ;

In some other part of your application, you can execute this query:

select @ts_diff;

that will return the value you are interested in.

Now, you can also create a function that executes the query and returns what you want, and call that function when you need it:

delimiter $$
create function ts_diff(id int) returns int
    declare diff int;
    select timestampdiff(MINUTE, fechaDeSolicitud, now()) <= 120
        into diff
    from pedidos
    where idPedido = id;
    return coalesce(diff, 0);
end $$

And you can execute this function whenever you need it in some other part of your application:

set @idPedido = 1; -- valor de prueba
select ts_diff(@idPedido);
answered by 23.05.2016 в 18:06