如何在MSSQL Server插入或更新触发器中精准更新目标行

嘿，这个问题很常见——触发器里没加WHERE子句确实会导致全表更新，这不仅效率低下，还可能意外修改无关数据。咱们来修改触发器，利用SQL Server内置的inserted和deleted系统表，精准定位仅被插入或修改的行：

1. 修改更新触发器（LocUpdated） #

在UPDATE语句中，通过关联inserted表（存储更新后的行数据）和目标表的主键，确保只更新被修改的行：

CREATE TRIGGER LocUpdated ON [SqlDistance].[dbo].[DistantTest]
AFTER UPDATE
AS
IF (UPDATE(Lat) OR UPDATE(Lon))
BEGIN
    UPDATE t
    SET [Geography2] = geography::STGeomFromText('POINT(' + CAST(i.Lon AS VARCHAR(20)) + ' ' + CAST(i.lat AS VARCHAR(20)) + ')', 4326)
    FROM [SqlDistance].[dbo].[DistantTest] t
    INNER JOIN inserted i ON t.ID = i.ID -- 这里的ID替换成你的表的实际主键列名
END;
GO

2. 修改插入触发器（LocInserted） #

插入操作的所有新行都存储在inserted表中，同样通过主键关联来更新对应的行：

CREATE TRIGGER LocInserted ON [SqlDistance].[dbo].[DistantTest]
AFTER INSERT
AS
BEGIN
    UPDATE t
    SET [Geography2] = geography::STGeomFromText('POINT(' + CAST(i.Lon AS VARCHAR(20)) + ' ' + CAST(i.lat AS VARCHAR(20)) + ')', 4326)
    FROM [SqlDistance].[dbo].[DistantTest] t
    INNER JOIN inserted i ON t.ID = i.ID -- 同样替换成你的表的实际主键列名
END;
GO

关键说明 #

• inserted系统表：每次触发插入或更新操作时，SQL Server会把新插入/更新后的行数据临时存在这里，我们可以通过它定位受影响的行。
• 主键关联：一定要用表的唯一主键列（比如示例中的ID）来关联目标表和inserted表，如果你的表主键不是ID，记得替换成实际的列名（比如RecordID、TestID等）。如果表没有主键，也可以用Lat和Lon的组合，但这可能存在重复值风险，建议优先添加主键。
• 效率提升：只更新受影响的行，避免了全表扫描和不必要的写入操作，大幅提升触发器性能。

内容的提问来源于stack exchange，提问作者Jose Manuel Ojeda