Spatial SQL -> MSSQL Spatial. Updatable View based on two table, one of them Spatial

In the following example we will create view that joins data from two tables. First tble c_data stores Addresses (city name, street, house number), the second c_geo stores address points (using Geometry type). Tables are linked by RecordId. First table is a main one, record in geometry table can exists or can not, so we use Left Join.
Our objective is to work with this dataset as with single updatable table: doing updates, inserts or deleting data. We can edit this data from the client machine using Map Editing software (Like MapInfo).

IF EXISTS (SELECT * FROM sys.objects
  WHERE object_id = OBJECT_ID(N'[dbo].[view_clients]')
DROP VIEW [dbo].[view_clients]
GO


CREATE VIEW [dbo].[view_clients]
AS
SELECT dbo.c_data.RecordId AS MI_PRINX,
  dbo.c_data.city, dbo.c_data.street, dbo.c_data.house,
  dbo.c_geo.MI_STYLE, dbo.c_geo.geom
FROM dbo.c_data LEFT JOIN dbo.c_geo
  ON dbo.c_data.RecordId = dbo.c_geo.RecordId
GO


We need to add two pieces of programming code in order to make our View really updatable. We will create two triggers that will run instead of UPDATE and instead of INSERT events of the View. The substitution of default behaviour by custom one is needed because of the Artificial nature of our "Table". view_clients is not a real table, it is a Join that need to be maintained properly. The issues that we have to solve are: 1. on Insert event add record into secondary table with correct RecordId, 2. on Update event chose between updating and inserting data into secondary table

CREATE TRIGGER [dbo].[trig_on_view_clients_Insert]
ON [dbo].[view_clients]
INSTEAD OF INSERT
AS
BEGIN
  DECLARE @id INT;

  -- table 1 Basic Data
  INSERT INTO dbo.c_data (city, street, house)
  SELECT city, street, house FROM INSERTED;

  -- get last inserted RecordId
  SELECT @id = MAX(RecordId) FROM dbo.c_data;

  -- table 2 Geometry
  INSERT INTO dbo.c_geo (RecordId, X, Y, MI_STYLE, geom)
  SELECT @id, geom.STX, geom.STY, MI_STYLE, geom
  FROM INSERTED;

END
GO


CREATE TRIGGER [dbo].[trig_on_view_clients_Update]
ON [dbo].[view_clients]
INSTEAD OF UPDATE
AS
BEGIN
  DECLARE @id INT;
  DECLARE @city Varchar(60), @street Varchar(60), @house SMALLINT;
  DECLARE @g GEOMETRY, @X FLOAT, @Y FLOAT, @MI_STYLE Varchar(200);

  -- get Record data
  SELECT @id = MI_PRINX,
  @city = city, @street = street, @house = house,
  @g = geom, @X = geom.STX, @Y = geom.STY, @MI_STYLE = MI_STYLE
  FROM INSERTED;

  -- table 1 Basic Data [Update Only]
  UPDATE dbo.c_data
  SET city = @city, street = @street, house = @house
  WHERE recordid = @id;

  -- table 2 Geometry [Update Or Insert]
  IF NOT EXISTS (select RecordId from dbo.c_geo where RecordId = @id)
  BEGIN
   INSERT INTO dbo.c_geo (RecordId, X, Y, MI_STYLE, geom)
   VALUES (@id, @X, @Y, @MI_STYLE, @g);
  END
  ELSE
  BEGIN
   UPDATE dbo.c_geo
   SET X = @X, Y = @Y, MI_STYLE = @MI_STYLE, geom = @g
   WHERE recordid = @id;
  END

END
GO





sqlexamples.info