Updateable Views - Part 40

In Part 39, we have discussed the basics of views. In this session we will learn about Updateable Views. Let's create tblEmployees table and populate it with some sample data.







Create Table tblEmployee Script:
CREATE TABLE tblEmployee
(
Id int Primary Key,
Name nvarchar(30),
Salary int,
Gender nvarchar(10),
DepartmentId int
)

Script to insert data:
Insert into tblEmployee values (1,'John', 5000, 'Male', 3)
Insert into tblEmployee values (2,'Mike', 3400, 'Male', 2)
Insert into tblEmployee values (3,'Pam', 6000, 'Female', 1)
Insert into tblEmployee values (4,'Todd', 4800, 'Male', 4)
Insert into tblEmployee values (5,'Sara', 3200, 'Female', 1)
Insert into tblEmployee values (6,'Ben', 4800, 'Male', 3)

Let's create a view, which returns all the columns from the tblEmployees table, except Salary column.
Create view vWEmployeesDataExceptSalary
as
Select Id, Name, Gender, DepartmentId
from tblEmployee

Select data from the view: A view does not store any data. So, when this query is executed, the database engine actually retrieves data, from the underlying tblEmployee base table.
Select * from vWEmployeesDataExceptSalary

Is it possible to Insert, Update and delete rows, from the underlying tblEmployees table, using view vWEmployeesDataExceptSalary?
Yes, SQL server views are updateable.

The following query updates, Name column from Mike to Mikey. Though, we are updating the view, SQL server, correctly updates the base table tblEmployee. To verify, execute, SELECT statement, on tblEmployee table.
Update vWEmployeesDataExceptSalary 
Set Name = 'Mikey' Where Id = 2

Along the same lines, it is also possible to insert and delete rows from the base table using views.
Delete from vWEmployeesDataExceptSalary where Id = 2
Insert into vWEmployeesDataExceptSalary values (2, 'Mikey', 'Male', 2)

Now, let us see, what happens if our view is based on multiple base tables. For this purpose, let's create tblDepartment table and populate with some sample data.
SQL Script to create tblDepartment table 
CREATE TABLE tblDepartment
(
DeptId int Primary Key,
DeptName nvarchar(20)
)

Insert data into tblDepartment table
Insert into tblDepartment values (1,'IT')
Insert into tblDepartment values (2,'Payroll')
Insert into tblDepartment values (3,'HR')
Insert into tblDepartment values (4,'Admin')

Create a view which joins tblEmployee and tblDepartment tables, and return the result as shown below.


View that joins tblEmployee and tblDepartment
Create view vwEmployeeDetailsByDepartment
as
Select Id, Name, Salary, Gender, DeptName
from tblEmployee
join tblDepartment
on tblEmployee.DepartmentId = tblDepartment.DeptId

Select Data from view vwEmployeeDetailsByDepartment
Select * from vwEmployeeDetailsByDepartment

vwEmployeeDetailsByDepartment Data:



Now, let's update, John's department, from HR to IT. At the moment, there are 2 employees (Ben, and John) in the HR department.
Update vwEmployeeDetailsByDepartment 
set DeptName='IT' where Name = 'John'

Now, Select data from the view vwEmployeeDetailsByDepartment:


Notice, that Ben's department is also changed to IT. To understand the reasons for incorrect UPDATE, select Data from tblDepartment and tblEmployee base tables.

tblEmployee Table


tblDepartment



The UPDATE statement, updated DeptName from HR to IT in tblDepartment table, instead of upadting DepartmentId column in tblEmployee table. So, the conclusion - If a view is based on multiple tables, and if you update the view, it may not update the underlying base tables correctly. To correctly update a view, that is based on multiple table, INSTEAD OF triggers are used.

We will discuss about triggers and correctly updating a view that is based on multiple tables, in a later video session.

16 comments:

  1. Sir, I an great fan of your videos. I have learnt so many things from it.
    I am very much thankful to you.
    Could you please explain what will happen when updating a computed column(e.g. calculated,decrypted)
    in a view?
    Thanks

    ReplyDelete
  2. An Update statement with View can effect on table data??

    ReplyDelete
  3. Sir i like your videos, i am following you..i have some doubt ,is it possible update ,delete ,insert on views it contains more tables? incase there is a chance how to do that? another one ..if changed columns data on views it getting effecting on base table so it is good or bad? if bad how to prevent that one.. another one .. i created view but some one changed my original table then view was effected so how to stop others didn't change base table data until my permissions...

    ReplyDelete
    Replies
    1. You will have to give the permissions to the tables to not update by the others.

      Delete
    2. view can be update then why we use instead of trigger

      Delete
    3. view can be update then why we use instead of trigger

      Delete
    4. Hi vitalreddy, you can use schemabinding option with view to prevent dependent tables from altered. You can view in more detail in next part.

      Delete
  4. Hi Vitalreddy,
    Venkat already explained this for functions.
    same can be applied here for view as well. I mean schema binding.

    alter view vWEmployeesByDepartment
    with schemabinding
    as

    select Id, Name, Salary, Gender, DeptName from dbo.tblEmployee E

    join dbo.tblDepartment D

    on D.DeptId = e.DepartmentId

    when you use shcemabinding your objects must containe 2 part names like dbo.tblEmployee

    ReplyDelete
  5. Thanks Sir for such well explanatory videos
    Today i attended an interview and i was asked if we have index on views and we need to update that view, then should we update the view after dropping index or not?
    I am not sure of this answer , it will be great if you can explain it with exapmles

    ReplyDelete
  6. Venkat Sir, Thank you very much for your videos. In this video of PART-40 Updatable views in SQL server at 5:05 in this video when we execute
    Insert into vWEmploteeesDataExceptSalary values (2, 'Mikey', 'Male',2) the values has inserted but what about the Salary field of Mikey, we have not supplied any value to that field, So it may get NULL or any other value?

    ReplyDelete
  7. Sir we have done update query vWEmploteeesDataExceptSalary , but is it possible to do Insert Record in tow Table...

    ReplyDelete
    Replies
    1. No.

      INSERT INTO vwEmployeeDetailsByDepartment(Id,Name,Salary, Gender, DeptName) VALUES(7,'Sam',6000,'Male','New')
      Msg 4405, Level 16, State 1, Line 9
      View or function 'vwEmployeeDetailsByDepartment' is not updatable because the modification affects multiple base tables.

      Delete
    2. DELETE from vwEmployeeDetailsByDepartment where Name = 'IT'
      Msg 4405, Level 16, State 1, Line 9
      View or function 'vwEmployeeDetailsByDepartment' is not updatable because the modification affects multiple base tables.

      Delete
  8. Awesome content. No one explains it so accurately with such good example!

    ReplyDelete

It would be great if you can help share these free resources