Update trigger does not update for all rows in a set based update query

Question

I have the tables Cities and Countries with the following triggers:

When I update any City, it should insert a row into the Audit table saying 'The City with ID 'any ID' has been modified'. Cities table has a foreign key relationship with Countries table (Cities.CountryID = Countries.ID).

What I expect trCountryUpdate to do is, when I update a Country row, it should insert row into the Audit table for all the Cities having the updated Country ID.

However, when I update the name of Country with ID 1, it should have inserted 5 rows into the Audit table as there are 5 corresponding Cities for CountryID 1. However, it always inserts 1 row.

Please let me know what should be done to insert 5 rows into Audit table for 5 cities having CountryID 1 when Country with ID 1 is updated.

PS:

ALTER trigger [dbo].[trCityUpdate]
on [dbo].[Cities]
for update
as

declare @cityIdModified int;
select @cityIdModified = ID from inserted;
Insert into Audit values ('The City with ID ' + cast(@cityIdModified as varchar(1)) + ' has been modified')

go

ALTER trigger [dbo].[trCountryUpdate]
on [dbo].[Countries]
for update
as

declare @countryIdModified int;
select @countryIdModified = ID from inserted;

update ci
set ci.CountryID = @countryIdModified
from Cities ci 
join Countries co on co.ID = ci.CountryID
where ci.CountryID = @countryIdModified

The 'Select @CityModified = ID from Inserted' returns only 1 value not the 5 of them. — Zeina, Oct 09 '18 at 07:17
Ok. Are there any other ways updating Country could insert 5 rows for each city in Audit table? without using cursor.. — MAK, Oct 09 '18 at 07:28
Please post code as (formatted) text, not as pictures. We can't copy/paste text in a picture, and pictures are not searchable. — HoneyBadger, Oct 09 '18 at 07:32
`inserted` contains *all* of the rows. Your assignments to scalar variables effectively take one row's values and ignore the rest. It's mean to be used like a *table*, and clearly you're already aware of joins. — Damien_The_Unbeliever, Oct 09 '18 at 07:37
You have to use inserted table. As Inserted table having all updated records. Modify trCityUpdate trigger as like below. Insert into Audit SELECT ('The City with ID ' + cast(ID as varchar(20)) + ' has been modified') from inserted — NP007, Oct 09 '18 at 07:42
Ah. Yes. It is done now. ALTER trigger [dbo].[trCityUpdate] on [dbo].[Cities] for update as Insert into Audit(Text) select 'The City with ID ' + cast(ID as varchar(1)) + ' has been modified' from inserted; Great. Thank you — MAK, Oct 09 '18 at 07:44

score 0 · Answer 1 · answered Oct 09 '18 at 07:53

Thank you for all your replies. It was a great help :)

ALTER trigger [dbo].[trCityUpdate]
on [dbo].[Cities]
for update
as

Insert into Audit(Text)
select 'The City with ID ' + cast(ID as varchar(1)) + ' has been modified' 
from inserted

When 5 rows in Cities table are updated, trCityUpdate is called only once, not 5 times. The inserted table contains 5 updated rows.

Update trigger does not update for all rows in a set based update query

1 Answers1