在同一个表上触发 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
    )
)

可以工作，但无法查询该列：/ 谢谢

Answer 1

尝试使用 PERSISTED COMPUTED 列： 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