Updateable views

By default view are updateable. It means you can write a TSQL update statement against a view just like you can update a table. I both cases you off course need update permissions. This give you an opportunity to let users update views containing less columns than the original table. You can update data in a view even if your dataset is a join on two or more tables.

In this blog we will se how you avoid users from update data out of your view.

First an example showing that you can empty your view. We create a table, insert some data, create a view, select data, update data and select again.

The first select will return all three rows. The second select will return zero rows while we change the country value to ‘Germany’ with now are included in the view.

CREATE TABLE t1 (RowID INT IDENTITY(1,1), CustomName VARCHAR(100), Country VARCHAR(100))
GO

INSERT INTO t1 (CustomName, Country) VALUES ('Peter', 'Denmark')
INSERT INTO t1 (CustomName, Country) VALUES ('Morten', 'Denmark')
INSERT INTO t1 (CustomName, Country) VALUES ('Svend', 'Denmark')
GO 

CREATE VIEW CustomersInDenmark AS
SELECT * 
FROM t1
WHERE Country = 'Denmark'
GO

SELECT * FROM CustomersInDenmark

UPDATE CustomersInDenmark SET Country = 'Germany'
GO 

SELECT * FROM CustomersInDenmark
GO

It could be the right solution to let the users update the Country information on all the customers but sometimes this is not what you want. Maybe these customers should be in Denmark. But you want to allow the users to update CustomerName.

Lets try to set back all customers to Denmark and then change the view, denying updates there will move data out of the view. This is done by the WITH CHECK OPTION

UPDATE t1 SET Country = 'Denmark'
GO

ALTER VIEW CustomersInDenmark AS
SELECT * 
FROM t1
WHERE Country = 'Denmark'
WITH CHECK OPTION
GO

UPDATE CustomersInDenmark SET Country = 'Germany'

Running this script will result in an error telling you that the view is using the WITH CHECK OPTION

You still can update columns not there is not effecting the rows membership of the view

UPDATE CustomersInDenmark SET CustomName = 'Bo'