INSTEAD OF triggers on views

Last time I wrote about updateable views – a feature that is not considered by many database architects and developers when they implement data interface. The updateable views have some limitations what makes them useful in only specific situations. Two most important limitations in my opinion are that you cannot update columns from more than one table in single update statement and that any derived or calculated columns cannot be directly updated.
Luckily, there is a way to bypass these limitations by using INDSTEAD OF triggers on views. The triggers are fired as the name implies instead of DML statement, so you have to implement effective operation within the trigger. If the trigger is empty, no update, insert or delete will be made.
Let’s look at Person.vContact2 view again. The view returns columns ContactId, NameStyle, Full Title, FirstName, MiddleName, LastName and several others. The Full Title column is a column that returns custom information depending on the underlying Title column from the Person.Contact table. You cannot update the Full Title column directly, because it doesn’t exist in the Person.Contact. If you want to change the information returned by the view, you have to modify the Title column in the table. Of course, you can update the table directly or via stored procedure and in most cases this would be the preferred way, but in some cases updating the view has its benefits. You may also have a requirement that the tables must not be updateable directly and the views are the only interface you can use to modify the data.
So, to change the Full Title column in the view, you have to modify the Title value appropriately. As you remember, the definition of the view is as follows:

   1: ALTER view [Person].[vContact2]

   2: as

   3: select ContactID, NameStyle, 

   4: case when Title = 'Mr.' then 'Very long title 1'

   5:     when Title = 'Ms.' then 'Very long title 2'

   6:     else 'Other title' end [Full Title], 

   7: FirstName, MiddleName, LastName, Suffix, EmailAddress, EmailPromotion, Phone, PasswordHash, PasswordSalt, rowguid, ModifiedDate

   8: from Person.Contact

Lines 4-6 contain definition of the Full Title column. In my AdventureWorks database, ContactID has ‘Other title’ returned by the view. I want to change it to ‘Very long title 1’.

You can create triggers for INSERT, UPDATE and DELETE statements. One of the ways is to create one trigger for all three types of operation, the other is to create a separate trigger for each of types separately. The latter approach simplifies logic within the trigger a bit, on the expense of maintainability – you have three database objects to worry about instead of one.

This is sample trigger that is fired for every DML operation against the view:

   1: create trigger Person.trgVContact2 on Person.vContact2

   2: instead of insert, update, delete

   3: as

   4: begin

   5:     print 'Trigger called'

   6: end

Now let’s call an update on the view:

   1: select * from Person.vContact2 where ContactId = 4

   2: update Person.vContact2 set LastName = 'NoName' where ContactId = 4

   3: select * from Person.vContact2 where ContactId = 4

Nice. The trigger was fired as you can see in the output. But, if you look at the LastName column, it was not changed. This is because this is an instead of trigger and it replaces the original operation. To actually update the column,  you have to implement the trigger in more useful way:

   1: if exists (select 1 from sys.objects where object_id = object_id('Person.trgVContact2'))

   2: drop trigger Person.trgVContact2

   3: go

   4: create trigger Person.trgVContact2 on Person.vContact2

   5: instead of insert, update, delete

   6: as

   7: begin

   8:     if (@@rowcount = 0)

   9:     begin

  10:         print 'No rows matching criteria'

  11:         return

  12:     end

  13:     if exists(select 1 from inserted) and exists (select 1 from deleted) --update operationr

  14:     begin

  15:         update contacts set LastName = inserted.LastName

  16:         from Person.Contact contacts inner join inserted on contacts.ContactId = inserted.ContactId 

  17:     end

  18:     else if exists(select 1 from inserted) --insert operation

  19:     begin

  20:         return; --TODO: implement with useful logic

  21:     end

  22:     else --delete operation

  23:     begin

  24:         return; --TODO: implement with useful logic

  25:     end

  26: end

As you see, this trigger allows for updating only LastName column. This column this column is exposed by the view without any modifications. On a side, did you know that triggers are fired even when no rows are affected? This was a surprise for me a few months ago. You can prevent potentially costly code from running if you check if there is anything to process. Lines 8-12 show how it can be done.

Ok, what about Full Title and other columns? This is the modified code for update operation only:

   1: if exists(select 1 from inserted) and exists (select 1 from deleted) --update operationr

   2:     begin

   3:         if update(PasswordHash) or update(PasswordSalt)

   4:         begin

   5:             print 'Password data changes are not permitted'

   6:             return

   7:         end

   8:         update contacts 

   9:             set NameStyle = inserted.NameStyle,

  10:                 Title = case inserted.[Full Title]

  11:                             when 'Very long title 1' then 'Mr.'

  12:                             when 'Very long title 2' then 'Ms.'

  13:                             else contacts.Title --no change in this case

  14:                         end,

  15:                 FirstName = inserted.FirstName,

  16:                 MiddleName = inserted.MiddleName,

  17:                 LastName = inserted.LastName,

  18:                 Suffix = inserted.Suffix,

  19:                 EmailAddress = inserted.EmailAddress,

  20:                 EmailPromotion = inserted.EmailPromotion,

  21:                 Phone = inserted.Phone,

  22:                 ModifiedDate = current_timestamp

  23:         from Person.Contact contacts inner join inserted on contacts.ContactId = inserted.ContactId 

  24:     end

Triggers offer you opportunity to validate what columns are updated. In certain scenarios, you don’t want users to be able to modify sensitive data. This can be achieved using triggers for example. In lines 3-7 of the above script you can see code preventing update operation on PasswordSalt and PasswordHash operations.

Ok, so far, so good. Triggers on views are one of the methods of implementing logic required to update data. However usually it is better to do it using stored procedures. There are scenarios though when triggers on views give you quite interesting ways of implementing ETL. More about this in next post.

Technorati Tags: sql server,programming,trigger,DDL,DML,t-sql,code