时间戳

首页 > 解决方案 > 如何在 MSSQL Server 插入或更新触发器上获取行 ID

sql-server - 如何在 MSSQL Server 插入或更新触发器上获取行 ID

问题描述

这里我有 2 个触发器，它们在插入或更新值后更新一个字段。我的问题是如何确保我只更新已插入或更新的行上的值，我的更新不包含任何 WHERE 子句

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

标签： sql-servertriggers

解决方案

你会想做一个JOIN到inserted. 这是伪 sql，在没有表 DDL 的情况下，但是，这应该让您走上正轨：

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;

推荐阅读