SQL insert trigger for insert with update

Question

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.

Are you making an assumption that only one (and just one - not 10 or 1000 or 0) row is ever inserted? And given the name "tot_donations" is there a 1:M relationship between the tables that would require summing to calculate a total? — SMor
– SMor, Commented Jun 19, 2020 at 14:36

GMB · Accepted Answer · 2020-06-19 14:20:23Z

2

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]

answered Jun 19, 2020 at 14:20

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL insert trigger for insert with update

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related