How to apply restrictions at time of Modifying Data using Views

Views do not maintain a separate copy of the data, but only display the data present in the base tables. Therefore, you can modify the base tables by modifying the data in the view, however, the following restrictions exist while inserting, updating, or deleting data through views:

  • You cannot modify data in a view if the modification affects more than one underlying table. However, you can modify data in a view if the modification affects only one table at a time.
  • You cannot change a column that is the result of a calculation, such as a computed column or an aggregate function.

For example, a view displaying the employee id, manger id, and rate of the employees has been defined using the following statement:

SELECT i.EmployeeID, i.MangerID, j.Rate FROM HumanResources.Employee AS i
JOIN HumanResources.EmployeePayHistory AS j ON
i.EmployeeID = j.EmployeeID

After creating the view, if you try executing the following update statement, it generates an error. This is because the data is being modified in two tables through a single update statement.

SET ManagerID = 2, Rate = 12.45
WHERE EmployeeID = 1

Therefore, instead of a single UPDATE statement, you need to execute two UPDATE statement for each table.

The following statement would update the EmployeeID attribute in the Employee base table:

SET ManagerID = 2
WHERE EmployeeID = 1

The following statement would update the Rate attribute in the EmployeePayHistory table:

SET Rate = 12.45
WHERE EmployeeID = 1

Therefore, to modify the data in two or more underlying tables through a view, you need to execute separate UPDATE statements for each table.


Popular posts from this blog

difference between structure and union in C Language

Difference between Linear search and Binary Search in c language

Difference between static and dynamic websites