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