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.
