Help with Trigger summing/subtracting

Asked

Viewed 833 times

0

I need help creating a Trigger in SQL Server that takes a value INTEIRO, and two STRING(FK), inserted in a tabela X, and check if the second value is 1 or 2, if it’s 1 she must add up this value INTEIRO with a value INTEIRO other’s Tabela Y who owns the STRING(FK) as PK, and whether the first String for 2 instead of summing it should subtract from the same place.

I have a lot of difficulty in the logic and structure of Trigger. I appreciate any help.

DETAILING (from the author’s comment)
Are two tables: Conta and Transação.
The table Conta has the column saldo and the table Transação has the columns valorDaTransacao, tipoTransacao (1 for withdrawal and 0 for deposit) and numeroConta, which is foreign key pointing to the table Conta.
Whenever a new row is created in the table Transação, the procedure Trigger must be triggered by subtracting or adding valorTransacao in the column saldo table Conta.

sql sql-server trigger

  • Could you expand the description of the problem? It still seems a little confusing. Add names of the tables and columns involved as well as the tables X and Y are related. And also example.

  • Put the table structure and code you already have ready

  • Tip, use CASE ... ((CASE WHEN X = 1 THEN 1 ELSE -1 END) * Y)

  • 1

    Hello guys, sorry! S]are two tables, Account Table and Transaction Table. The Account Table has in one of its columns, the BALANCE column, and in the Transaction Table it has a Transaction column. In the Transaction Table, there is a FK (numeroConta) that is PK(numeroConta) in the Account table, and also a column (typTransaction) that can be 1 (DRAW) or 0 (DEPOSIT). My goal is that whenever you create a new record in the Transaction Table, a Trigger is triggered by SUBTRACTING or ADDING the valueTransaction in the Account Table Balance column. Best described agr?

1 answer

1


The procedure Trigger then it is to monitor the inclusion of rows in the Transaction table.

-- código #1 v2
CREATE TRIGGER incTransacao_Conta
    on Transacao 
    after INSERT as
begin
-- encerra o processamento se não há linha para tratar
IF not exists (SELECT * from INSERTED) return;
--
UPDATE C
  set saldo+= case I.tipoTransacao 
                   when 1 then -I.valorDaTransacao 
                   when 0 then I.valorDaTransacao 
                   else 0 end
  from Conta as C 
       inner join INSERTED as I on I.numeroConta = C.numeroConta;
end;

Reading suggestion:

  • Thanks for the help! I managed to create Trigger, I used this code:

Browser other questions tagged sql sql-server trigger

You are not signed in. Login or sign up in order to post.