Updateable viewsPosted: November 9, 2012
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'