CODEWITHSUNDEEP
sql-server
swdev
swdev

Reputation: 5157

Why does my trigger always insert zero value in SQL Server?

I am rather confused, because my trigger in SQL Server cannot insert the value what I expected it would. The situation is as follows :

What confuses me is that the trigger will only insert the correct amount of value if the situation is saldo, but not if the situation is buy

What did I do wrong? Here is the code:

declare @last_saldo int
declare @transaction_ammount int

set @last_saldo = (select sum(saldo_ammount) from saldo)
if @last_saldo is null set @last_saldo=0

set @transaction_ammount = (select transaction_ammount from inserted)
IF (select transaction_type from inserted) = 'Saldo'
begin
/* this will insert correct amount */
INSERT INTO saldo
    (id_transaction,transaction_type,saldo_ammount,saldo)
SELECT id_transaction,'Debit',@transaction_ammount,@last_saldo + @transaction_ammount
FROM inserted
RETURN 
END else IF (select transaction_type from inserted)='Buy'
begin
    /* this will not insert the correct ammount. It will always zero! */
INSERT INTO saldo
    (id_transaction,transaction_type,saldo_ammount,saldo)
SELECT id_transaction,'Credit',@transction_ammount,(@last_saldo - @transaction_ammount)
FROM inserted
RETURN 
END

Many Thanks!

Upvotes: 1

Views: 379

Answers (1)

p.campbell
p.campbell

Reputation: 100607

Perhaps you can refactor your trigger to be a bit simpler:

declare @last_saldo int

select @last_saldo = ISNULL(sum(saldo_ammount),0)
 from saldo

INSERT INTO saldo
        (id_transaction,transaction_type,saldo_ammount,saldo)

    SELECT id_transaction,
           CASE WHEN transaction_type = 'Saldo' 
             THEN 'Debit'
             ELSE 'Credit' 
           END,
           transaction_ammount,
           CASE WHEN transaction_type = 'Saldo' 
             THEN (@last_saldo + transaction_ammount)
             ELSE (@last_saldo - transaction_ammount)
           END
    FROM inserted
RETURN

Is the problem of zero solved with this code? If not, determine what @last_saldo and transaction_ammount values are. That'll lead you to the root of your problem.

Caveat: be aware that inserted can have more than one row!

Upvotes: 1

Related Questions