SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Updating a view

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.


Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: code language, language sql, microsoft sql server, sql statements, T-SQL

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...