| ||
You can insert values into views in the same way that you insert values into tables. However, you must be careful to only insert values for columns that belong to a single table. If a view includes several tables, the insert values can only manage columns from one of the tables. For example, the CategoryName column cannot be updated in the following view.
Open SQL Server Management Studio and connect to your server.
Click the New Query button and type the following:
USE [AdventureWorks] GO CREATE VIEW [dbo].[vProductFullCategories] AS
SELECT Production.ProductSubcategory.ProductSubcategoryID, Production.ProductCategory.Name AS CategoryName, Production.ProductSubcategory.Name FROM Production.ProductSubcategory INNER JOIN Production.ProductCategory ON Production.ProductSubcategory.ProductCategoryID = Production.ProductCategory.ProductCategoryID GO
Try to insert a row by using the following sentence :
INSERT INTO [AdventureWorks].[Production].[vProductFullCategories] ([ProductSubcategoryID] ,[CategoryName] ,[Name]) VALUES (1 ,'Bikes' ,'My New Subcategory')
You will receive the following error:
Msg 4405, Level 16, State 1, Line 1 View or function 'AdventureWorks.Production.vProductFullCategories' is not updatable because the modification affects multiple base tables.
You can use a WITH clause to obtain recursive information to insert into another table. This is a good way to reconstruct multilevel relational information into a single table.
Consider the following example: The managers at Adventure Works want to send a special Christmas e-mail to all employees. They decide that each manager should send the e-mail to his own employees. You can create a table containing information about each managers employees and the senders e-mail address.
The table can be constructed as follows :
CREATE TABLE [Person].[ChristmasMails]( [EMailID] [int], [FirstName] [dbo].[Name] NOT NULL, [LastName] [dbo].[Name] NOT NULL, [EmailAddress] [nvarchar](50) NULL, [EmailSender] [nvarchar](50) NULL ) GO
Using a common table expression, you can then build the entire list of destinations, with the senders e-mail address included as one of the columns in the query. Since one of the employees is the top manager, that person will not receive an e-mail and the senders address will be an empty string.
WITH MailDestinations(EmployeeID, FirstName, LastName, EmailAddress, EMailSender) AS ( SELECT HumanResources.Employee.EmployeeID, Person.Contact.FirstName, Person.Contact.LastName, Person.Contact.EmailAddress, CONVERT(nvarchar(50),'') AS EMailSender FROM HumanResources.Employee INNER JOIN Person.Contact ON HumanResources.Employee.ContactID = Person.Contact.ContactID WHERE (HumanResources.Employee.ManagerID IS NULL) UNION ALL SELECT e.EmployeeID, Person.Contact.FirstName, Person.Contact.LastName, Person.Contact.EmailAddress, d.EmailAddress AS EmailSender FROM HumanResources.Employee AS e INNER JOIN Person.Contact ON e.ContactID = Person.Contact.ContactID JOIN MailDestinations AS d ON e.ManagerID = d.EmployeeID ) INSERT INTO [Person].[ChristmasMails] SELECT * FROM MailDestinations
The first SELECT statement selects the information about the top manager who has no assigned Manager ID. The second section makes recursive queries to obtain information about those employees who are supervised by the top manager. The query then recurses to obtain information about the employees of those who are already in the common table expression until no more employees are found. The execution of this query will add 290 records to the [Person].[ChristmasMails] table.
Suppose that you use the vProductFullCategories statement to give users the opportunity to obtain information from an Office application, such as Excel. However, the user tries to use the same view from Microsoft Access, assuming he has no rights to insert information into the tables. In fact, the user can do that if he has insert rights on the view. Access tries to insert values for all of the columns, including those belonging to other tables.
You can use an INSTEAD OF INSERT trigger on the view to discard those values. Moreover, you can manage the insertion in several related tables inside a view. The trigger, like any trigger, uses a special table named inserted that receives the values defined by the user. The trigger can select from this table to insert the appropriate values in one or more tables.
Adding the following INSTEAD Of INSERT trigger allows you to manage the insertion properly.
CREATE TRIGGER [Production].[vProductFullCategoriesInsertTrigger] on [Production].[vProductFullCategories] INSTEAD OF INSERT AS BEGIN DECLARE @idCategory INT -- Retrieve the id from the ProductCategory table SET @idCategory= (SELECT ProductCategoryID FROM Production.ProductCategory WHERE (Name = (SELECT CategoryName FROM inserted) ) ) -- Insert the proper values INSERT INTO [AdventureWorks].[Production].[ProductSubcategory] ([ProductCategoryID] ,[Name] ,[rowguid] ,[ModifiedDate]) SELECT @IdCategory ,inserted.name ,newid() ,getdate() FROM inserted END
| ||