I am trying to create a trigger where the account balance is automatically updated from the transaction table, but I am not being able to update a particular table. My trigger changes the account balance of every customer. I want to know how I can use where clause to make it customer-specific. Here is what I have:
create trigger trig_one
on dbo.transactions
for insert, update, delete
as begin
declare @amount int
select @amount = (select amount from inserted)
update dbo.account set balance = balance + @amount
end
Please help me out. Thanks!
You structure the query like this:
update dbo.account
set balance = balance + i.amount
from dbo.account a join
inserted i
on i.accountid = a.accountid ;
(The join key is whatever the join key should be.)
Of course, you need to do the same thing for the deleted. Note: this will work for multiple rows being inserted. That is handy. Your version will fail unexpectedly.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments