Trigger with MySQL query

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'@'127.0.0.1' FUNCTION 'menor_a_120'(fechaDeSolicitud datetime) RETURNS int(11)
BEGIN
DECLARE valor INT;
    SET @diferencia_minutos=(SELECT timestampdiff(MINUTE, fechaDeSolicitud, now()) );
    IF (@diferencia_minutos<=120) THEN
        SET valor=0;
    ELSE
        SET valor=1;
    END IF;
RETURN valor;
END  

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.

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

CREATE TRIGGER trigger_pedidos AFTER INSERT ON pedidos
    BEGIN
        DECLARE v1 INT DEFAULT 1;
        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;
    END;

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

link

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
begin
    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
begin
    declare diff int;
    select timestampdiff(MINUTE, fechaDeSolicitud, now()) <= 120
        into diff
    from pedidos
    where idPedido = id;
    return coalesce(diff, 0);
end $$
delimiter;

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);