Updating a view

2

September 30, 2015 by Kenneth Fisher

I was recently asked how to update views. Specifically the question was about using triggers to update a view.

So updating a simple view is easy enough. Per BOL:

Updatable Views
  • You can modify the data of an underlying base table through a view, as long as the following conditions are true:
  • Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.
  • The columns being modified in the view must directly reference the underlying data in the table columns. The columns cannot be derived in any other way, such as through the following:
    • An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, and VARP.
    • A computation. The column cannot be computed from an expression that uses other columns. Columns that are formed by using the set operators UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation and are also not updatable.
  • The columns being modified are not affected by GROUP BY, HAVING, or DISTINCT clauses.
  • TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause.

 
To put it very simply we can update a view if we update one table at a time and the view isn’t doing anything to the columns we are updating. But what do we do if we need to go beyond these restrictions? We can in fact use triggers. Specifically INSTEAD OF TRIGGERS. An INSTEAD OF TRIGGER basically replaces the command it is defined on. So for example an INSTEAD OF UPDATE trigger replaces the update command on the table or view it’s associated with.

For a simple example I’m going to try to update [AdventureWorks2014].[Sales].[vStoreWithContacts]. Note this particular view hits a number of different tables. I’m going to run an update that updates the name in the Store table and the first name of the Person table.

UPDATE [Sales].[vStoreWithContacts] 
SET Name = 'Next-Door Bike Store2',
	FirstName = 'Gustavo2'
WHERE BusinessEntityID = 292;

Which gives me the error:

Msg 4405, Level 16, State 1, Line 10
View or function ‘Sales.vStoreWithContacts’ is not updatable because the modification affects multiple base tables.

If I want this to work I can put in an INSTEAD OF trigger: (Note this is a VERY simple trigger that doesn’t do more than a fraction of what it should do.)

CREATE TRIGGER [Sales].[tr_vStoreWithContacts]
ON [Sales].[vStoreWithContacts] 
INSTEAD OF UPDATE
AS
UPDATE s
SET s.Name = i.Name
FROM [Sales].[Store] s
JOIN inserted i
	ON s.BusinessEntityID = i.BusinessEntityID;

UPDATE p
SET p.FirstName = i.FirstName
FROM [Person].[Person] p
JOIN [Person].[BusinessEntityContact] bec 
	ON p.[BusinessEntityID] = bec.[PersonID]
JOIN inserted i
	ON bec.BusinessEntityID = i.BusinessEntityID;
GO

Now we try again.

UPDATE [Sales].[vStoreWithContacts] 
SET Name = 'Next-Door Bike Store2',
	FirstName = 'Gustavo2'
WHERE BusinessEntityID = 292;

And no error this time and the data changes exactly as expected.

2 thoughts on “Updating a view

  1. Ray Herring says:

    Hi Ken,
    Nice concise post on a neat technique.

    I think it would be a good idea to at least generally describe what types of additional processing should be added to make the trigger production ready.
    I assume some sort of error handling (e.g., constraint Violation, data truncation, …) .
    Should (or can) the updates be enclosed in an Explicit Transaction? An update to a single table would naturally be an atomic operation but in this case it is updating two tables.
    Perhaps some sort of validation on the Where conditions?
    Anyway, just saying the code does not do “a fraction of what it should” seems a little weak. 😉

    • Thanks for the comment. Yea, it probably was a bit weak. You have to remember though that triggers can be tricky things and I wouldn’t want to put one out there that someone will take and dump into their systems without testing/looking at first. For example I probably wouldn’t put a transaction in place since I believe the implicit transaction of the insert/update/delete would cover the trigger (I’d have to test). Error handling would be on an as needed basis since most of the error handling (data types etc) is going to be handled by the tables themselves. Although you could of course put in your own.

      I’ll try to come up with a best practices trigger but it may be a bit.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,655 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: