Trigger for INSERT and UPDATE on same table

Question

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.

Regards

Edit:

script looks like that:


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

works but can't query that column :/ Thx

Your trigger will cause data integrity problems if any process ever updates or inserts a batch of records. Never write a trigger assuming only one record will be in inserted. — HLGEM, Apr 06 '11 at 21:09
Vote for this Connect item: http://connect.microsoft.com/SQLServer/feedback/details/378126/how-to-persist-a-calculated-geometry-or-geography-column — Cade Roux, Apr 07 '11 at 01:02

Cade Roux · Accepted Answer · 2011-04-06T23:48:41.157

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.

Triggers: 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

@john a persisted computed column will be recalculated any time a row is updated, just like a trigger - have a look at that Books Online link. A trigger can be smarter and not calculate things by checking to see if certain columns changed, but a trigger also has to ensure it is operating over the entire inserted pseudo table and not assuming only a single row. Only thing you might need to check is that the geometry function is deterministic. `CAST/CONVERT` `VARCHAR` to `DATETIME` for instance is not deterministic UNLESS a fixed format is specified. — Cade Roux, Apr 06 '11 at 21:31
@john I posted alternative trigger code here for you as well. — Cade Roux, Apr 06 '11 at 21:46
@cade sorry but I get an error if I try to show the table content in visual studio. I will update my first post with what I have done. — john, Apr 06 '11 at 22:07
@john My test shows a manual version of the column is the same as a calc version - I'm not sure what you are seeing. I'll add this code to my answer — Cade Roux, Apr 06 '11 at 23:47
@cade apparently I can't use this, because it is not possible to create an index on a computed column. :/ But I will keep this in mind. Nevertheless the trigger works :) thanks again — john, Apr 07 '11 at 00:50
@john Apparently this entire question is a 3-year-old exact duplicate: http://stackoverflow.com/questions/230992/how-to-persist-a-calculated-geometry-or-geography-column — Cade Roux, Apr 07 '11 at 01:00

Trigger for INSERT and UPDATE on same table

1 Answers1

Linked