Restrictions on Updating Data Through Views - SQL

You can insert, update, and delete rows in a view, subject to the following limitations:

• If the view contains joins between multiple tables, you can only insert and update one table in the view, and you can't delete rows.
• You can't directly modify data in views based on union queries. You can't modify data in views that use GROUP BY or DISTINCT statements.
• All columns being modified are subject to the same restrictions as if the statements were being executed directly against the base table.
• Text and image columns can't be modified through views.
• There is no checking of view criteria. For example, if the view selects all customers who live in Paris, and data is modified to either add or edit a row that does not have City = 'Paris', the data will be modified in the base table but not shown in the view, unless WITH CHECK OPTION is used when defining the view.

Using WITH CHECK OPTION

The WITH CHECK OPTION clause forces all data-modification statements executed against the view to adhere to the criteria set within the WHERE clause of the SELECT statement defining the view. Rows cannot be modified in a way that causes them to vanish from the view. Listing 9.12 creates a view showing customers from Paris using the WITH CHECK OPTION statement.

Listing 9.12 Creating a View Using WITH CHECK OPTION

CREATE VIEW vwCustomersParis
AS
 SELECT CompanyName, ContactName, Phone, City
 FROM Customers
 WHERE City = 'Paris'

WITH CHECK OPTION

The following Transact-SQL statement attempting to update data by moving everyone from Paris to Lyons will fail because Lyons does not meet the criteria defined in the view. If you did not have WITH CHECK OPTION defined, the UPDATE statement would succeed, and a requery of the view would return no rows. Here's the statement:

UPDATE vwCustomersParis
SET City = 'Lyons'

You may have noticed in Access that placing criteria on the RecordSource query of a form limits the records that are displayed in the form when you open it, but it doesn't limit what records can be added in the form. Using an Access project or an Access database with a form bound to a view that contains WITH CHECK OPTION would allow you to automatically have the criteria enforced for new or updated records.

Example :

create table Employee ( 

id int identity(1,1) primary key,

Name varchar(500)

)

create table Salary 

( 

id int identity(1,1) primary key,

Employee_id int ,

Amount int,

foreign key(Employee_id) REFERENCES Employee(id) 

)

insert into Employee values ('Kumar')

insert into Employee values ('Naveen')

insert into Employee values ('Madhu')

select * from Employee 

insert into Salary values (1,10000)

insert into Salary values (2,20000)

insert into Salary values (3,30000)

select * from Salary 

alter view VEmpSalary 

AS

select e.id,e.Name,s.Amount from Employee e 

join Salary s on e.id = s.Employee_id

select * from VEmpSalary

update VEmpSalary set Amount = 15000 where ID=1

select * from VEmpSalary

update VEmpSalary set name = 'Kumar',Amount = 18000 where ID=1