Since the prior responce had nothing to do with the problem, I’ve removed it.
I think you’ll find this code works for you (be sure to test it before putting it into production, I don’t have any data to test against).
CREATE TRIGGER UpdateAfterPayment On SupplierTransactionDetails
For Insert, Update, Delete
S.Balance = case when i.DebitValue>0 then
S.Balance + i.DebitValue
S.Balance - i.CreditValue
from SupplierTransactionHead H
join inserted i On H.transectionID=i.TransectionID
You’ll see that I removed the Suppliers table from the FROM field. It should be up as part of the UPDATE and then referenced via the WHERE to link the values in Suppliers and SupplierTransactionHead together.
I’ve also removed the IF from the update statement. You need to use the CASE statement instead. When using the CASE statement within an update you specify the destination column = then the case. You’ll see that I’ve for when the DebitValue > 0 all the DebitValue, otherwise subtract the Credit Value (make sure I’ve got that correct).
You may find this article on the UPDATE statement useful.