CODEWITHSUNDEEP
mysqlsqltriggers
Oracy Martos
Oracy Martos

Reputation: 457

Trigger with query data

I have table with the informations: 

INSERT INTO Transferencias
('Origem_Apostador_ID', 'Destino_Apostador_ID', 'Valor_Transferido')
VALUES
('1', '6', '200.00');

And I need to get value '200.00' and sum with the data from another table after this value have been insert on table to this table below:

INSERT INTO Conta
('Apostador_ID', 'Saldo', 'Qtd_Saque', 'Qtd_Deposito', 'Qtd_Transferencia')
VALUES
('1', '700.00', '0', '1', '0');

I have no idea how can I use data from query to do the update on "Saldo" column.

Upvotes: 0

Views: 427

Answers (1)

Thalles Ribeiro
Thalles Ribeiro

Reputation: 46

I don`t know if I understood your question correctly, but i think you need something like this:

UPDATE Conta 
   SET Saldo = Saldo + (SELECT Valor_Transferido FROM Transferencias WHERE Origem_Apostador_ID = '1') 
 WHERE Apostador_ID = '1';

You can add this code to some trigger if you want to. You can find many examples out there for simple triggers to do the work depending on your database.

TRIGGER EXAMPLE ON MYSQL

CREATE TRIGGER TRG_UPDATE_SALDO BEFORE INSERT ON Transferencias
FOR EACH ROW
   BEGIN
       IF NEW.Valor_Transferido > 0 THEN

    --Increments the destination account
    UPDATE Conta 
       SET Saldo = Saldo + NEW.Valor_Transferido 
     WHERE Apostador_ID = NEW.Destino_Apostador_ID;

    --Subtracts the origin account
    UPDATE Conta 
       SET Saldo = Saldo - NEW.Valor_Transferido 
     WHERE Apostador_ID = NEW.Origem_Apostador_ID;
       END IF;
   END;

Upvotes: 1

Related Questions