Database , event or routine ? would work?

Question

I am new to create event and routines in the database and I came up with a question I am making a payment system and I wanted to know if it is possible to put this function as an event or a routine the code would be but or less like

    select idvencedordesafiante as vencedordesafiado,idvencedordesafiado as vencedordesafiante,valor as receber,id as idp from partidas where aceitou=2
if(vencedordesafiado==vencedordesafiante){

UPDATE `usuarios` SET `Saldo`=receber WHERE id= vencedordesafiado
UPDATE `partidas` SET `aceitou`=3 WHERE id= idp
}

someone who understands more would tell me if it would work ?

Answer 1

You can create a Trigger for your table, as below:

CREATE TRIGGER `partidas_before_insert` BEFORE INSERT ON `partidas` FOR EACH ROW BEGIN
  if(new.aceitou = 2)then
    if(new.idvencedordesafiante = new.idvencedordesafiado){
      UPDATE 'usuarios' SET 'Saldo' = new.valor WHERE id = new.idvencedordesafiado
      UPDATE 'partidas' SET 'aceitou' = 3 WHERE id = new.id
     end if;
  end if;
END

This is just an example, can have a little mistake or other, try to understand and apply correctly to your case.

But I created an example of a Rigger on your match table, where it will be run whenever a record is included in it.

What is a Rigger?

A TRIGGER is a database object associated with a table, set to be fired, responding to an event in particular. Such events are the commands of DML (Data Manipulation Language): INSERT, REPLACE, DELETE or UPDATE. We can define numerous TRIGGERS in a database based directly on which of the above commands will fire it, and for each, we can set only a TRIGGER. TRIGGERS can be triggered to work before or after the event.

You can read a few more things about triggers(Triggers) here and here

EDITED

As you asked in the comments, I made this example of an Event Scheduler to run the updates you want:

SET GLOBAL event_scheduler = ON;


CREATE DEFINER=`root`@`localhost` EVENT `partidas`
ON SCHEDULE
    EVERY 1 MINUTE STARTS '2018-10-01 13:00:00'
ON COMPLETION PRESERVE
ENABLE
COMMENT ''
DO BEGIN
  select @vencedordesafiado  := coalesce(idvencedordesafiante, 0)
        ,@vencedordesafiante := coalesce(idvencedordesafiado, 0)
        ,@receber            := coalesce(valor, 0)
        ,@idp                := coalesce(id, 0)
   from partidas where aceitou = 2

  if(@idp <> 0)then
    if(@vencedordesafiado = @vencedordesafiante)
      UPDATE 'usuarios' SET 'Saldo' = @receber WHERE id = @vencedordesafiante
      UPDATE 'partidas' SET 'aceitou' = 3 WHERE id = @idp
     end if;
  end if;
END

Note: I set the example using limit 1 to bring only one line in select, then you will have to adjust your filter or make a loop to go through the line.

Again, it’s an example, so improve it according to your needs and change the event start time to a few minutes after you create it to run correctly.

You can give a read on events here