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 :
I have
transaction
tab开发者_如何学Cle which can have two types of transactions in it -saldo
andbuy
. If it issaldo
, the trigger intransaction
table will insert the amount of the transaction total tosaldo
table, but withDebit
in itssaldo_type
field.So if the case in
transaction
table isbuy
, the same amount will be inserted insaldo
table, but withcredit
in itssaldo_type
field.
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!
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!
精彩评论