Database, event, or routine? would work?

Question

Database, event, or routine? would work?

Navigation

#1 by (2 votes)

0

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

    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
}

Anyone who understands the most would know if it would work?

asked by anonymous 01.10.2018 / 15:50

1 answer

Detect field type sent in POST PHP How to use a template as a type on a map?

score 2 · Accepted Answer

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, you can have a little one or another, try to understand and apply correctly to your case.

But I created an example of a trigger in your matches table, where it will be executed whenever a record is included in it.

What is a trigger?

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

You can read a few more things about triggers here and here

EDITED

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

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 the select, so you'll have to set your filter or loop to go line by line.

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

You can read about events here