title: Trigger for INSERT and UPDATE on same table
body: <p>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.</p>
  
  <pre>
  <code>
  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
  </code>
  </pre>
  
  <p>Would be glad if someone could help me.</p>
  
  <p>Regards</p>
  
  <p><strong>Edit:</strong></p>
  
  <p>script looks like that:</p>
  
  <pre>
  <code>
  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
      )
  )
  </code>
  </pre>
  
  <p>works but can't query that column :/
  Thx</p>