CODEWITHSUNDEEP
sql-servertriggers
Jose Manuel Ojeda
Jose Manuel Ojeda

Reputation: 328

how to get the row id on a MSSQL Server insert or update trigger

Here I have 2 triggers, they update a field after the values are inserted or update. my question is how can I make sure that I'm only updating the value on the row that has been inserted or update, my update does not include any WHERE clause 

CREATE TRIGGER LocUpdated  
ON [SqlDistance].[dbo].[DistantTest]  
AFTER  UPDATE   
AS   
IF ( UPDATE (Lat) OR UPDATE (Lon))  
BEGIN  

UPDATE [SqlDistance].[dbo].[DistantTest] 
SET [Geography2] = geography::STGeomFromText('POINT(' + CAST([Lon] AS VARCHAR(20)) + ' ' + 
                    CAST([lat] AS VARCHAR(20)) + ')', 4326)
END;  
GO 


CREATE TRIGGER LocInserted  
ON [SqlDistance].[dbo].[DistantTest]  
AFTER INSERT
AS   
BEGIN  

UPDATE [SqlDistance].[dbo].[DistantTest] 
SET [Geography2] = geography::STGeomFromText('POINT(' + CAST([Lon] AS VARCHAR(20)) + ' ' + 
                    CAST([lat] AS VARCHAR(20)) + ')', 4326)
END;  
GO

Upvotes: 0

Views: 662

Answers (1)

Thom A
Thom A

Reputation: 95554

You'll want to do a JOIN onto inserted. This is pseudo-sql, in the absence of table DDL, however, this should get you on the right track:

UPDATE D
SET [Geography2] = geography::STGeomFromText('POINT(' + CAST(i.[Lon] AS VARCHAR(20)) + ' ' + 
                    CAST(i.[lat] AS VARCHAR(20)) + ')', 4326)
FROM [dbo].[DistantTest] D --no need to declare the database, we're already in it.
     JOIN inserted i ON D.UniqueIdColumn = i.UniqueIdColumn;

Upvotes: 1

Related Questions