Deleting Data through Views

You can also delete rows from tables by using views. However, the following restrictions apply.

• You can delete rows from only one table.

  Consider the following view (included in the sample files as \Ch11\AdvWorks\Queries\Sample05.sql):

   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 

  Delete one row by using the following sentence :

   DELETE TOP (1) FROM vProductFullCategories 

  You will receive the following error:

   Msg 4405, Level 16, State 1, Line 1 View or function 'vProductFullCategories' is not updatable because the modification affects multiple base tables. 

• The connected user must have delete rights over the view in question.

  Assume that a user has read-only rights over a view. The following code is included in the sample files as \Ch11\AdvWorks\Queries\Sample06.sql.

   USE [master] GO CREATE LOGIN [ReadOnlyUser] WITH PASSWORD=N'ReadOnlyUser' MUST_CHANGE, DEFAULT_DATABAS E=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON GO USE [AdventureWorks] GO EXEC sp_addrolemember N'db_datawriter', N'ReadOnlyUser' GO use [AdventureWorks] GO DENY DELETE ON [dbo].[vProductFullCategories] TO [ReadOnlyUser] GO 

  The ReadOnlyUser will not be allowed to delete anything from the view.

• The views creator must have delete rights over the table.

Using INSTEAD OF DELETE Triggers on Views

There are ways to get around the limitations placed on deleting information by using a view. To delete a subcategory from a view, you can create an INSTEAD OF trigger to process the request and perform the delete action over the subcategory s data table only.

As with any other trigger, the table named deleted contains the row or rows intended to be deleted when the trigger is fired . Using that table, you can retrieve the ProductSubcategoryID and use it to delete the row in the ProductSubcategory table. The following code is included in the sample files as \Ch11\AdvWorks\Queries\Sample07.sql.

 Use AdventureWorks GO CREATE TRIGGER [dbo].[vProductFullCategoriesInsertDeleteTrigger]    ON [dbo].[vProductFullCategories] INSTEAD OF DELETE AS BEGIN    DECLARE @Id int    SET @Id=(SELECT [ProductSubcategoryID] FROM deleted)    DELETE FROM Production.ProductSubcategory WHERE ProductSubcategoryID = @Id END GO 

However, a problem exists with this trigger. Try to execute the following sentence:

 DELETE TOP (10) FROM vProductFullCategories 

You will receive the following error:

 Msg 512, Level 16, State 1, Procedure vProductFullCategoriesInsertDeleteTrigger, Line 7 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated. 

When the trigger attempts to assign the @Id variable, an error is raised because the SELECT statement returns more than one row!

When creating a trigger, you must keep in mind that the trigger will be fired when a delete action is performed and not when a single row is to be deleted. The correct syntax for the trigger must establish a relationship between the table to be deleted and the table named deleted. The following code is included in the sample files as \Ch11\AdvWorks\Queries\Sample08.sql.

Tip 

Any object used in a SQL Server sentence must be part of the FORM clause. In the following second version, having the table named deleted linked to ProductSubcategory through the INNER JOIN operation allows you to use the ProductSubcategory in the trigger code.

 ALTER TRIGGER [dbo].[vProductFullCategoriesInsertDeleteTrigger]    ON [dbo].[vProductFullCategories] INSTEAD OF DELETE AS BEGIN    DELETE Production.ProductSubcategory       FROM Production.ProductSubcategory       INNER JOIN deleted       ON Production.ProductSubcategory.ProductSubcategoryID =             deleted.ProductSubcategoryID END GO