Monday, June 28, 2010

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.


  1. Hi Piotr,

    Nice post!

    Can you send me an e-mail when you get a chance? adam [at] sqlblog [dot] com

    Adam Machanic

  2. Thanks Adam, I sent you email :)

  3. Hi Piotr,

    Nice one, simple and insteresting!


  4. Nice post.

    You're right, it is a feature that you don't see being used that often.

    They can be really useful when wanting to refactor/change your database design eg base tables - whilst still wanting to maintain an existing data contract to external contracts via views.

  5. I agree. Views are a fantastic way of decoupling implementation from the higher levels of application. As Adam Machanic says, database should be regarded as data interface rather than data store. That's why I am opposing to use OR mappers wherever possible, they have nasty habit of relying on bare tables.

  6. Good point.

    I always try to follow the principle of not giving applications direct access to base tables. Ideally data interfaces should be via sprocs but for some OR mappers there can be issues with sprocs. In these cases I try to encourage the use of views. With 'instead of triggers' you can then refactor/change base tables whilst maintaing the data interface offered by the view.

  7. I've used INSTEAD OF triggers for specific purposes - mainly for ruby-on-rails-style db<>app interfaces. A big plus over sprocs is that they handle set-based database operations coming in from the app.

    The point about dropping out of the trigger if there is nothing to do is very important for performance reasons. Three other points are: (1) triggers receive two ROWSETS of data ([inserted] table and [deleted] table), so all operations inside the trigger must cater for this (i.e. be set-based or loop through the rows), (2) There may still be triggers on the individual tables that are affected, and these will be fired, (3) Obviously you can call sprocs from the trigger.

    As others have implied, keeping one set of business logic when multiple end-user systems are in place, often means that, in practice, it sits with the database. How do folks feel about this?