在同一表上触发INSERT和UPDATE [英] Trigger for INSERT and UPDATE on same table

问题描述

众所周知，实体框架无法保存地理位置数据。所以我的想法是，在模型中将经度和纬度指定为十进制。在执行了用于创建表的SQL脚本之后，我将启动另一个用于添加地理位置列的脚本。然后，我想通过触发器在每个INSERT或UPDATE（经度和纬度）上更新此列。以下触发器可以吗，还是不好？我问是因为我对触发器不是很熟悉，但是它现在可以使用。

as all of us know, entity framework can't hold geography data. So my idea was, to specify the longitude and latitude as decimal in my model. After executing the SQL script for creating the tables I would start another script for adding a geography column. Then I would like to update this column on every INSERT or UPDATE (on longitude and latitude) by a trigger. Is the following trigger okay, or is it something bad? I'm asking because I'm not very familiar with trigger, but it works for now.

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

如果有人可以帮助我，我将非常高兴。

Would be glad if someone could help me.

致谢

编辑：

脚本如下所示：

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
    )
)

可以，但无法查询该列：/
Thx

works but can't query that column :/ Thx

推荐答案

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

Try a PERSISTED COMPUTED column: http://msdn.microsoft.com/en-us/library/ms191250.aspx (might need an outer cast here)

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
)

这避免了触发具有几乎相同的总体效果。

This avoids having to make a trigger with pretty much the same overall effect.

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

您的触发器可能是修改为：

Your trigger could probably be modified as:

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

以下示例显示了手动列和计算列：

Here's an example showing both manual and calc'ed columns:

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

这篇关于在同一表上触发INSERT和UPDATE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！