Recently there was a disscussion at the georeference.org on how to provide a user / user group SQL SERVER data access encapsulation in manifold (and pretty much in any other client) so different clients modify the very same data source but do not have the access to other users data.
The answer was - the updatable views:
https://msdn.microsoft.com/en-us/library/ms180800(v=sql.110).aspx#Restrictions
https://msdn.microsoft.com/en-us/library/ms187956(v=sql.110).aspx
When a view is created with the 'WITH VIEW_METADATA' then it becomes update able straight away but there are some restrictions (see links above). The other option is to use 'INSTEAD OF' triggers. It is way more flexible, though one needs to create the triggers of course ;)
In manifold, due to the way it inserts the data - first goes a geom with nulls for all the fields, then goes the data collected through the 'instant data' window or the direct object / table edit - objects are not populated with the data needed to filter the dataset and therefore are not editable / delete able until the data is fixed on the DB side and a drawing / table is refreshed. This pretty much is a show stopper with the 'WITH VIEW_METADATA' approach .
Luckily there are the 'INSTEAD OF' triggers. The most important is the insert trigger - this is the place the fields used for view filtering are populated. One can then opt for a full set of the 'INSTEAD OF' triggers or use a mixed approach - triggers with the view metadata.
A longer version with a complete set of the 'INSTEAD OF' triggers:
--cleanup, cleanup, everybody cleanup
-------------------------------------
IF OBJECT_ID('dbo.vwTest_TypeX', 'V') IS NOT NULL
DROP VIEW [dbo].[vwTest_TypeX];
GO
IF OBJECT_ID('dbo.vwTest_TypeY', 'V') IS NOT NULL
DROP VIEW [dbo].[vwTest_TypeY];
GO
IF OBJECT_ID('dbo.tblTest', 'U') IS NOT NULL
DROP TABLE [dbo].[tblTest];
GO
-------------------------------------
--create test table
-------------------------------------
CREATE TABLE [dbo].[tblTest](
[OID] [int] IDENTITY(1,1) NOT NULL,
[Version] [int] NULL,
[Type] [nvarchar](1) NULL, --Type is used to handle user / user group data encapsulation
[Geometry] [geometry] NULL,
PRIMARY KEY CLUSTERED
(
[OID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
-------------------------------------
--views that will be updatable through the INSTEAD OF triggers
-------------------------------------
--View for Team X
CREATE VIEW [dbo].[vwTest_TypeX] as
SELECT
[OID],
[Version],
--Do not select [Type]! Type is used to provide user / user group data encapsulation and should
--not be handled by the users but rather automaticaly!
[Geometry]
FROM
[dbo].[tblTest]
--Note:
--can do joins, aggregates and such
WHERE
[Type] = 'X';
GO
--View for Team Y
CREATE VIEW [dbo].[vwTest_TypeY] as
SELECT
[OID],
[Version],
--Do not select [Type]! Type is used to provide user / user group data encapsulation and should
--not be handled by the users but rather automaticaly!
[Geometry]
FROM
[dbo].[tblTest]
--Note:
--can do joins, aggregates and such
WHERE
[Type] = 'Y';
GO
-------------------------------------
--create INSTEAD OF triggers
-------------------------------------
--team X
-------------------------------------
--instead of insert
CREATE TRIGGER [dbo].[vwTest_TypeX_Insert]
ON [dbo].[vwTest_TypeX]
INSTEAD OF INSERT AS
BEGIN
--Note:
--need to do an insert to the source table not the view, as the vie is supposed to hide the
--[Type] column from users;
--if this was not the case could interact with the view directly as in update / delete triggers
INSERT INTO [dbo].[tblTest] ([Version], [Type], [Geometry])
SELECT
[Version],
'X', --need to apply the type or other criteria used by the view to filter out the
--subset of the data
[Geometry]
FROM
INSERTED;
--Note:
--can modify other tables and such
IF @@ERROR<>0
RAISERROR('Failed on inserting into [vwTest_TypeX].', 16, 1);
END
GO
--insetad of delete
CREATE TRIGGER [dbo].[vwTest_TypeX_Delete]
ON [dbo].[vwTest_TypeX]
INSTEAD OF DELETE AS
BEGIN
DELETE FROM
[dbo].[vwTest_TypeX]
WHERE
[OID] in (SELECT [OID] FROM DELETED);
--Note:
--can modify other tables and such
IF @@ERROR<>0
RAISERROR('Failed on deleting from [vwTest_TypeX].', 16, 1);
END
GO
--instead of update
CREATE TRIGGER [dbo].[vwTest_TypeX_Update]
ON [dbo].[vwTest_TypeX]
INSTEAD OF UPDATE AS
BEGIN
IF UPDATE([OID])
RAISERROR('Cannot update [OID] on [vwTest_TypeX].', 16, 1);
UPDATE
[dbo].[vwTest_TypeX]
SET
[Version] = ins.[Version],
[Geometry] = ins.[Geometry]
FROM
INSERTED ins join [dbo].[vwTest_TypeX] tbl on ins.OID = tbl.[OID]
--Note:
--can modify other tables and such
IF @@ERROR<>0
RAISERROR('Failed on updating [vwTest_TypeX].', 16, 1);
END
GO
-------------------------------------
--team Y
-------------------------------------
--instead of insert
CREATE TRIGGER [dbo].[vwTest_TypeY_Insert]
ON [dbo].[vwTest_TypeY]
INSTEAD OF INSERT AS
BEGIN
--Note:
--need to do an insert to the source table not the view, as the vie is supposed to hide the [Type] column from users;
--if this was not the case could interact with the view directly as in update / delete triggers
INSERT INTO [dbo].[tblTest] ([Version], [Type], [Geometry])
SELECT
[Version],
'Y', --need to apply the type or other criteria used by the view to filter out the
--subset of the data
[Geometry]
FROM
INSERTED;
--Note:
--can modify other tables and such
IF @@ERROR<>0
RAISERROR('Failed on inserting into [vwTest_TypeY].', 16, 1);
END
GO
--insetad of delete
CREATE TRIGGER [dbo].[vwTest_TypeY_Delete]
ON [dbo].[vwTest_TypeY]
INSTEAD OF DELETE AS
BEGIN
DELETE FROM
[dbo].[vwTest_TypeY]
WHERE
[OID] in (SELECT [OID] FROM DELETED);
--Note:
--can modify other tables and such
IF @@ERROR<>0
RAISERROR('Failed on deleting from [vwTest_TypeY].', 16, 1);
END
GO
--instead of update
CREATE TRIGGER [dbo].[vwTest_TypeY_Update]
ON [dbo].[vwTest_TypeY]
INSTEAD OF UPDATE AS
BEGIN
IF UPDATE([OID])
RAISERROR('Cannot update [OID] on [vwTest_TypeY].', 16, 1);
UPDATE
[dbo].[vwTest_TypeY]
SET
[Version] = ins.[Version],
[Geometry] = ins.[Geometry]
FROM
INSERTED ins join [dbo].[vwTest_TypeY] tbl on ins.OID = tbl.[OID]
--Note:
--can modify other tables and such
IF @@ERROR<>0
RAISERROR('Failed on updating [vwTest_TypeY].', 16, 1);
END
GO
-------------------------------------
And a shorter version that mixes the 'WITH VIEW_METADATA' and only the 'INSTEAD OF INSERT' trigger
--cleanup, cleanup, everybody cleanup
-------------------------------------
IF OBJECT_ID('dbo.vwTest_TypeX', 'V') IS NOT NULL
DROP VIEW [dbo].[vwTest_TypeX];
GO
IF OBJECT_ID('dbo.vwTest_TypeY', 'V') IS NOT NULL
DROP VIEW [dbo].[vwTest_TypeY];
GO
IF OBJECT_ID('dbo.tblTest', 'U') IS NOT NULL
DROP TABLE [dbo].[tblTest];
GO
-------------------------------------
--create test table
-------------------------------------
CREATE TABLE [dbo].[tblTest](
[OID] [int] IDENTITY(1,1) NOT NULL,
[Version] [int] NULL,
[Type] [nvarchar](1) NULL, --Type is used to handle user / user group data encapsulation
[Geometry] [geometry] NULL,
PRIMARY KEY CLUSTERED
(
[OID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
-------------------------------------
--views that will be updatable through the INSTEAD OF triggers
-------------------------------------
--View for Team X
CREATE VIEW [dbo].[vwTest_TypeX]
WITH VIEW_METADATA
AS
SELECT
[OID],
[Version],
--Do not select [Type]! Type is used to provide user / user group data encapsulation and should not be handled by the users but rather automaticaly!
[Geometry]
FROM
[dbo].[tblTest]
--Note:
--can do joins, aggregates and such
WHERE
[Type] = 'X';
GO
--View for Team Y
CREATE VIEW [dbo].[vwTest_TypeY]
WITH VIEW_METADATA
AS
SELECT
[OID],
[Version],
--Do not select [Type]! Type is used to provide user / user group data encapsulation and should not be handled by the users but rather automaticaly!
[Geometry]
FROM
[dbo].[tblTest]
--Note:
--can do joins, aggregates and such
WHERE
[Type] = 'Y';
GO
-------------------------------------
--create INSTEAD OF triggers
-------------------------------------
--team X
-------------------------------------
--instead of insert
CREATE TRIGGER [dbo].[vwTest_TypeX_Insert]
ON [dbo].[vwTest_TypeX]
INSTEAD OF INSERT AS
BEGIN
--Note:
--need to do an insert to the source table not the view, as the vie is supposed to hide the [Type] column from users;
--if this was not the case could interact with the view directly as in update / delete triggers
INSERT INTO [dbo].[tblTest] ([Version], [Type], [Geometry])
SELECT
[Version],
'X', --need to apply the type or other criteria used by the view to filter out the subset of the data
[Geometry]
FROM
INSERTED;
--Note:
--can modify other tables and such
IF @@ERROR<>0
RAISERROR('Failed on inserting into [vwTest_TypeX].', 16, 1);
END
GO
-------------------------------------
--team Y
-------------------------------------
--instead of insert
CREATE TRIGGER [dbo].[vwTest_TypeY_Insert]
ON [dbo].[vwTest_TypeY]
INSTEAD OF INSERT AS
BEGIN
--Note:
--need to do an insert to the source table not the view, as the vie is supposed to hide the [Type] column from users;
--if this was not the case could interact with the view directly as in update / delete triggers
INSERT INTO [dbo].[tblTest] ([Version], [Type], [Geometry])
SELECT
[Version],
'Y', --need to apply the type or other criteria used by the view to filter out the subset of the data
[Geometry]
FROM
INSERTED;
--Note:
--can modify other tables and such
IF @@ERROR<>0
RAISERROR('Failed on inserting into [vwTest_TypeY].', 16, 1);
END
GO
-------------------------------------