在同一张表上进行INSERT和UPDATE的触发器

Question

众所周知，实体框架无法保存地理数据。因此我的想法是，在我的模型中将经度和纬度指定为十进制数。在执行用于创建表的SQL脚本后，我会开始另一个脚本以添加地理列。然后，我想通过触发器在每次插入或更新（经度和纬度）时更新此列。以下触发器是否可以，还是有什么问题？我问这个问题是因为我对触发器不太熟悉，但它现在运作良好。

触发器如下：

CREATE TRIGGER Update_Geography 
ON [People]
FOR INSERT, UPDATE
AS
BEGIN
    DECLARE @longitude DECIMAL(8, 5), @latitude DECIMAL(8, 5)
SET @longitude = (SELECT ins.Location_Longitude FROM inserted ins)
    SET @latitude = (SELECT ins.Location_Latitude FROM inserted ins)
IF (@longitude != 0 AND @latitude != 0)
    BEGIN
        UPDATE [People]
        SET
            Location_Geography = geography::STGeomFromText('POINT(' + CONVERT(VARCHAR(100),@longitude) + ' ' +  CONVERT(VARCHAR(100),@latitude) + ')',4326)
        WHERE
            Id = (SELECT ins.Id FROM inserted ins)
    END
END

如果有人能帮我，我会很高兴。

问候

编辑：

脚本如下：

ALTER TABLE [People] ADD Location_Geography AS (
    CONVERT(GEOGRAPHY, CASE
        WHEN Location_Latitude  0 AND Location_Longitude  0 THEN
            geography::STGeomFromText('POINT(' + CONVERT(VARCHAR, Location_Longitude) + ' ' + CONVERT(VARCHAR, Location_Latitude) + ')',4326)
        ELSE
            NULL
    END
    )
)

这是可以工作的，但无法查询该列 :/ 谢谢

- john

---

如果任何进程更新或插入一批记录，您的触发器将导致数据完整性问题。永远不要编写一个假设只有一个记录被插入的触发器。 - HLGEM

请为此Connect项目投票：http://connect.microsoft.com/SQLServer/feedback/details/378126/how-to-persist-a-calculated-geometry-or-geography-column - Cade Roux

Answer 1

尝试使用持久化计算列：http://msdn.microsoft.com/en-us/library/ms191250.aspx（可能需要在此处进行外部转换）。

Location_Geography AS (
    CASE
        WHEN Location_Latitude <> 0 AND Location_Longitude <> 0 THEN
            geography::STGeomFromText('POINT(' + CONVERT(VARCHAR(100),Location_Longitude) + ' ' +  CONVERT(VARCHAR(100),Location_Latitude) + ')',4326)
        ELSE
        NULL
    END
)

这样可以避免创建一个具有几乎相同整体效果的触发器。

触发器：http://msdn.microsoft.com/en-us/library/ms191524.aspx

您的触发器可能需要进行修改：

CREATE TRIGGER Update_Geography 
ON [People]
FOR INSERT, UPDATE
AS
BEGIN
        UPDATE [People]
        SET
            Location_Geography = geography::STGeomFromText('POINT(' + CONVERT(VARCHAR(100),Location_Longitude) + ' ' +  CONVERT(VARCHAR(100),Location_Latitude) + ')',4326)
        WHERE (UPDATE(Location_Longitude) OR UPDATE(Location_Latitude))
            AND Id IN (SELECT ins.Id FROM inserted ins)
    END
END

这里有一个例子展示了手动和计算列的使用：

IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'[dbo].[SO5572806]')
                    AND type IN (N'U') ) 
    DROP TABLE [dbo].[SO5572806]
GO

CREATE TABLE SO5572806
    (
     lo DECIMAL(8, 5) NOT NULL
    ,la DECIMAL(8, 5) NOT NULL
    ,man GEOGRAPHY NULL
    ,calc AS (CONVERT(GEOGRAPHY, CASE WHEN la <> 0
                                        AND lo <> 0
                                   THEN GEOGRAPHY::STGeomFromText('POINT('
                                                              + CONVERT(VARCHAR, lo)
                                                              + ' '
                                                              + CONVERT(VARCHAR, la)
                                                              + ')', 4326)
                                   ELSE NULL
                              END))
    )
GO

INSERT  INTO dbo.SO5572806
        (lo, la)
VALUES  (0, 0),
        (-90, 30)

UPDATE  dbo.SO5572806
SET     man = GEOGRAPHY::STGeomFromText('POINT(' + CONVERT(VARCHAR, lo) + ' '
                                      + CONVERT(VARCHAR, la) + ')', 4326)
WHERE   lo <> 0
        AND la <> 0

SELECT  *
FROM    dbo.SO5572806

- Cade Roux

---

但是如果经度和纬度发生变化呢？ - john

@john 持久化计算列会在每次更新行时重新计算，就像触发器一样 - 可以查看Books Online链接。 触发器可以更智能地进行操作，通过检查某些列是否更改来避免计算，但是触发器还必须确保它正在操作整个插入的伪表，而不是仅假定单个行。您需要检查的唯一事情可能是几何函数是否确定性。例如，将VARCHAR转换为DATETIME的CAST / CONVERT不是确定性的，除非指定了固定格式。 - Cade Roux

@john，我也在这里为你发布了另一种触发器代码的替代方案。 - Cade Roux

@cage 抱歉，如果我尝试在Visual Studio中显示表格内容，会出现错误。我将更新我的第一篇帖子以展示我所做的工作。 - john

@cade 显然我不能使用这个，因为无法在计算列上创建索引。 :/ 但是我会记住这一点的。尽管如此，触发器还是起作用的 :) 再次感谢 - john