Instead of triggers over views (part 1)


Views are useful for creating a business entity based view data while allowing for an efficient logical schema. However, views are normally not updatable–limiting their utility. However, SQL Server’s instead of triggers allow many of these views to be updatable. SQL Server view-based instead of triggers can be a tricky to use. Below are some experiments that show how to write them and some of their properties. A complete script is attached.


In instead of triggers, two pseudo-tables inserted and deleted contain the before and after values of the operation. The update and columns_updated functions indicate if a column is updated by the operation causing the trigger to fire.


Imagine the following table:


create table [Test].[TestsTable]


(


  [Id]     int not null identity constraint [PK_TestsTable] primary key,


  [Value1] nvarchar(100) not null,


  [Value2] nvarchar(100),


  [Value1and2] as [Value1] + [Value2]


);


with the following view:


create view [Test].[Tests]


as


  select T.[Id], T.[Value1], T.[Value2]


  from [Test].[TestsTable] as T;


The following instead of triggers make the view updatable. The highlighted portions are to show the behavior of the inserted and deleted pseudo-tables and the update and columns_updated functions:


create trigger [Test].[InsertTestTrigger] on [Test].[Tests]


  instead of insert as


begin


  select case when update([Id]) then N’yes’ else N’no’ end as [Id Updated],


         case when update([Value1]) then N’yes’ else N’no’ end  as [Value1 Updated],


         case when update([Value2]) then N’yes’ else N’no’ end  as [Value2 Updated],


         [Test].[BinaryToBinaryNumber](columns_updated()) as [Column Update Mask];


 


  select *


  from inserted


  order by [Id];


 


  select *


  from deleted


  order by [Id];


 


  insert into [Test].[TestsTable] ([Value1], [Value2])


    select I.[Value1], I.[Value2]


    from inserted as I;


end;


go


 


create trigger [Test]. [UpdateTestTrigger] on [Test].[Tests]


  instead of update as


begin


  select case when update([Id]) then N’yes’ else N’no’ end as [Id Updated],


         case when update([Value1]) then N’yes’ else N’no’ end  as [Value1 Updated],


         case when update([Value2]) then N’yes’ else N’no’ end  as [Value2 Updated],


         [Test].[BinaryToBinaryNumber](columns_updated()) as [Column Update Mask];


 


  select *


  from inserted


  order by [Id];


 


  select *


  from deleted


  order by [Id];


 


  if update([Id])


  begin


    raiserror (N’Error: May not updatethe column [Test].[TestsTable].[Id]’, 16, 0);


  end


  else


  begin


    update [Test].[TestsTable]


      set [Value1] = I.[Value1],


          [Value2] = I.[Value2]


      from inserted as I


      where [Test].[TestsTable].[Id] = I.[Id];


  end;


end;


go


 


create trigger [Test].[DeleteTestTrigger] on [Test].[Tests]


  instead of delete as


begin


  select case when update([Id]) then N’yes’ else N’no’ end as [Id Updated],


         case when update([Value1]) then N’yes’ else N’no’ end  as [Value1 Updated],


         case when update([Value2]) then N’yes’ else N’no’ end  as [Value2 Updated],


         [Test].[BinaryToBinaryNumber](columns_updated()) as [Column Update Mask];


 


  select *


  from inserted


  order by [Id];


 


  select *


  from deleted


  order by [Id];


 


  delete from [Test].[TestsTable]


    from deleted as D


    where [Test].[TestsTable].[Id] = D.[Id];


end;


Suppose the following insert statement is executed.


insert into [Test].[Tests] ([Value1], [Value2])


  values (N’1-1′, N’1-2′),


         (N’2-1′, null);


Then the following update pattern is the result. Because this is an insert operation all columns are marked as updated. Notice that the bit corresponding to the first column is the low bit not the high bit. Also, the bit set is based on the column ID not the column ordinal.















Id Updated


Value1 Updated


Value2 Updated


Column Update Mask


Yes


Yes


Yes


0b00000111


 


The inserted pseudo-table is:

















Id


Value1


Value2


0


1-1


1-2


0


2-1


NULL


 


The deleted pseudo-table is empty:













Id


Value1


Value2


 


 


 


 


The resulting view is:

















Id


Value1


Value2


1


1-1


1-2


2


2-1


NULL


 


Suppose the following insert statement is executed.


insert into [Test].[Tests] ([Value1])


  values (N’3-1′),


         (N’4-1′);


Then the following update pattern is the result. Because this is an insert operation all columns are marked as updated even unspecified columns.















Id Updated


Value1 Updated


Value2 Updated


Column Update Mask


Yes


Yes


Yes


0b00000111


 


The inserted pseudo-table is:

















Id


Value1


Value2


0


3-1


NULL


0


4-1


NULL


 


The deleted pseudo-table is:













Id


Value1


Value2


 


 


 


 


The resulting view is:

























Id


Value1


Value2


1


1-1


1-2


2


2-1


NULL


3


3-1


NULL


4


4-1


NULL


 


Suppose the following update statement is executed.


update [Test].[Tests]


  set [Value1] += N’ updated’;


The following update pattern is the result. Only the Value1 column is marked as updated.















Id Updated


Value1 Updated


Value2 Updated


Column Update Mask


No


Yes


No


0b00000010


 


The inserted pseudo-table is:

















Id


Value1


Value2


1


1-1 updated


1-2


2


2-1 updated


NULL


 


The deleted pseudo-table is:

















Id


Value1


Value2


1


1-1


1-2


2


2-1


NULL


 


The resulting view is:

























Id


Value1


Value2


1


1-1 updated


1-2


2


2-1 updated


NULL


3


3-1


NULL


4


4-1


NULL


 


Suppose the following delete statement is executed.


delete from [Test].[Tests]


  where [Id] > 2;


The following update pattern is the result. Since this is a delete operation no column is marked as updated.















Id Updated


Value1 Updated


Value2 Updated


Column Update Mask


No


No


No


0b


 


The inserted pseudo-table is:













Id


Value1


Value2


 


 


 


 


The deleted pseudo-table is:

















Id


Value1


Value2


3


3-1


NULL


4


4-1


NULL


 


The resulting view is:

















Id


Value1


Value2


1


1-1 updated


1-2


2


2-1 updated


NULL


 


In the next part I will look at some issues you need to be aware of when working with instead of triggers.

Instead of trigger demo code.sql

Comments (1)