0

I am writing an after insert trigger trying to find a solution to this problem here:

https://stackoverflow.com/questions/19355644/dead-ends-all-around-trying-to-update-geography-column

What I am unsure of is how to write the trigger to take into consideration multiple records as explained here as a potential you need to code for.

So far I had this but it applies only to a single record so if the table had 100 records inserted in a batch 99 would not be updated. This is my understanding so far and may not be correct.

create trigger tri_inserts on [dbo].[Address]
after insert
as
set nocount on

update Address
SET AddyGeoCode = GEOGRAPHY::Point(inserted.AddyLat, inserted.Addylong, 4326)
GO

Would I say join to the inserted table to discover / update all the new records?

In case it is needed my Address table schema is AddyLat & AddyLong decimal(7,4) and AddyGeoCode Geography.

TIA

Community
  • 1
  • 1
GPGVM
  • 5,515
  • 10
  • 56
  • 97

1 Answers1

2

Yes, you need to join on inserted table.

UPDATE a
SET a.AddyGeoCode = GEOGRAPHY::Point(a.AddyLat, a.Addylong, 4326) --you can use AddyLat&Long from either a or i
FROM Address a 
INNER JOIN inserted i ON a.id = i.id --whatever are your PK columns
Nenad Zivkovic
  • 18,221
  • 6
  • 42
  • 55