Updating Data through Views

You can perform update actions by using views in the same way that you create insert or delete actions. However, the following limitations apply.

• Updates are only allowed on columns from a single table.

• The connected user must have write access to the view.

• The view creator must have write access to the table.

Using INSTEAD OF UPDATE Triggers on Views

Just as you can personalize certain actions involving views, you can manage and change the behavior of update actions on views by using INSTEAD OF UPDATE triggers. In this case, the update action will respond like any other trigger by using a double-phase action: the engine deletes the old row and then adds a new one.

Although this last statement is not precisely accurate, it does reflect the representation you obtain with the trigger procedure. According to this, you will use both a virtual table named deleted and a virtual table named inserted. The first table will contain the old version of the row, and the second table will contain the new version of the row as it will look when the update procedure is completed. To understand this concept, consider the following scenario.

Understanding the INSTEAD OF UPDATE Trigger

1. Call an UPDATE statement.

2. The engine copies the old row into a virtual table named deleted.

3. The engine copies the old row again into another virtual table named inserted.

4. The engine performs the changes in the row inside the table named inserted with the values you pass to the UPDATE statement.

5. The engine calls your INSTEAD OF trigger and passes it to both the table named deleted and the table named inserted.

6. If your INSTEAD OF trigger is successful, the engine calls the AFTER triggerif one existsin the table named deleted and also in the table named inserted with the changes you made in the INSTEAD OF trigger.

7. If this last trigger is successful or if no AFTER trigger exists, the transaction ends and all of the changes commit to the database.

The syntax is similar to that used for the other INSTEAD OF triggers. This code is included in the sample files as \Ch12\AdvWorks\Queries\Sample01.sql.

 Use AdventureWorks GO CREATE TRIGGER [dbo].[vProductFullCategoriesUpdateTrigger]    ON [dbo].[vProductFullCategories] INSTEAD OF UPDATE AS BEGIN    UPDATE Production.ProductSubcategory    SET Name=inserted.Name    FROM    Production.ProductSubcategory       INNER JOIN inserted       ON Production.ProductSubcategory.ProductSubcategoryID=             inserted.ProductSubcategoryID END 

Detecting Changes in a View

In the previous trigger sample, you assumed that the user will change the subcategory s name. However, the view shows the category name, which the user can also change. To determine exactly what columns have been changed, you can use the UPDATE(<column>) function. This function returns true if the specified column has been changed in the update process.

Using the UPDATE(<column>) function, the trigger can decide which table to update by using the following syntax. This code is included in the sample files as \Ch12\AdvWorks\Queries\Sample02.sql.

 Use AdventureWorks GO ALTER TRIGGER [dbo].[vProductFullCategoriesUpdateTrigger]    ON [dbo].[vProductFullCategories] INSTEAD OF UPDATE AS BEGIN    IF UPDATE(Name)       BEGIN          UPDATE Production.ProductSubcategory          SET Name=inserted.Name          FROM          Production.ProductSubcategory             INNER JOIN inserted             ON Production.ProductSubcategory.ProductSubcategoryID                =inserted.ProductSubcategoryID       END    IF UPDATE(CategoryName)       BEGIN          UPDATE Production.Productcategory          SET Name=inserted.Name          FROM          Production.ProductSubcategory             INNER JOIN inserted             ON Production.ProductSubcategory.ProductSubcategoryID                =inserted.ProductSubcategoryID          INNER JOIN Production.ProductCategory             ON Production.ProductCategory.ProductCategoryID                =Production.ProductSubcategory.ProductCategoryID       END END 

Note 

Transact -SQL (T-SQL) supports the COLUMNS_UPDATED function to test several updated columns at the same time. This function returns a varbinary datatype with one or more bytes with the bitwise results of the updated columns. That is, if the update changes the first column, the rightmost bit of the byte is turned on, and so forth. To check the value, you must use bitwise operations. Search for COLUMNS_UPDATED in SQL Server Books Online for more information and examples.