The problem was what you have observed before - it could not update the newly inserted record as the view was filtering it out. In theINSTEAD OF insert trigger it is therefore important to populate the data that is used for filtering. Then everything is works like a charm ;) Try the code below (tested with sqlserver 2k12, but it should work ok for 2k8, 2k14 too). Does exactly what you need. Provides the user / user group encapsulation you are after and is ok with inserts updates and deletes. It creates 2 views that read from one source table and use INSTEAD OF triggers to manage CRUD. --Some reading --Updatable views: https://msdn.microsoft.com/en-us/library/ms180800(v=sql.110).aspx#Restrictions --CREATE VIEW: https://msdn.microsoft.com/en-us/library/ms187956(v=sql.110).aspx --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 -------------------------------------
maps made easy - www.cartomatic.pl || www.cartoninjas.net |