CODEWITHSUNDEEP
sqlsql-serversql-updatesql-insertdatabase-trigger
ron zuk
ron zuk

Reputation: 9

SQL insert trigger for insert with update

I have two tables:

[1] Donations - with amount and pet_id field

[2] Pets - with id and total donations field

I'm trying to create a trigger which will update total donations field whenever a new row is being inserted to Donations table. I tried this one:

create trigger update_donations
on sponserships
for insert
as
   update dbo.Pets 
   set tot_donations = (
      select new_val = inserted.amount + pets.tot_donations
      from inserted
      where inserted.[Pet-ID] = pets.[Animal-ID]
   )

But of course it changes all records, whereas i want to change only records that are changed in the donations table.

Upvotes: 0

Views: 38

Answers (1)

GMB
GMB

Reputation: 222462

It is usually not a good practice to store this type of derived information - you could have a view that computes it on the fly rather than a trigger that keeps it up to date. Also please note that if you go that way, you also need a delete and an update trigger...

That said, you can use the following query in your insert trigger to update the relevant record in the pets table:

update p
set p.total_donations = p.total_donations + i.amount    
from dbo.Pets p
inner join inserted i on i.[Pet-ID] = p.[Animal-ID]

Upvotes: 2

Related Questions