5 min læsning

Updateable views

Featured Image

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.

<span style="color:blue;">CREATE TABLE </span><span style="color:teal;">t1 </span><span style="color:gray;">(</span><span style="color:teal;">RowID </span><span style="color:blue;">INT IDENTITY</span><span style="color:gray;">(</span>1<span style="color:gray;">,</span>1<span style="color:gray;">), </span><span style="color:teal;">CustomName </span><span style="color:blue;">VARCHAR</span><span style="color:gray;">(</span>100<span style="color:gray;">), </span><span style="color:teal;">Country </span><span style="color:blue;">VARCHAR</span><span style="color:gray;">(</span>100<span style="color:gray;">))
</span><span style="color:blue;">GO

INSERT INTO </span><span style="color:teal;">t1 </span><span style="color:gray;">(</span><span style="color:teal;">CustomName</span><span style="color:gray;">, </span><span style="color:teal;">Country</span><span style="color:gray;">) </span><span style="color:blue;">VALUES </span><span style="color:gray;">(</span><span style="color:red;">'Peter'</span><span style="color:gray;">, </span><span style="color:red;">'Denmark'</span><span style="color:gray;">)
</span><span style="color:blue;">INSERT INTO </span><span style="color:teal;">t1 </span><span style="color:gray;">(</span><span style="color:teal;">CustomName</span><span style="color:gray;">, </span><span style="color:teal;">Country</span><span style="color:gray;">) </span><span style="color:blue;">VALUES </span><span style="color:gray;">(</span><span style="color:red;">'Morten'</span><span style="color:gray;">, </span><span style="color:red;">'Denmark'</span><span style="color:gray;">)
</span><span style="color:blue;">INSERT INTO </span><span style="color:teal;">t1 </span><span style="color:gray;">(</span><span style="color:teal;">CustomName</span><span style="color:gray;">, </span><span style="color:teal;">Country</span><span style="color:gray;">) </span><span style="color:blue;">VALUES </span><span style="color:gray;">(</span><span style="color:red;">'Svend'</span><span style="color:gray;">, </span><span style="color:red;">'Denmark'</span><span style="color:gray;">)
</span><span style="color:blue;">GO 

CREATE VIEW </span><span style="color:teal;">CustomersInDenmark </span><span style="color:blue;">AS
SELECT </span><span style="color:gray;">*
</span><span style="color:blue;">FROM </span><span style="color:teal;">t1
</span><span style="color:blue;">WHERE </span><span style="color:teal;">Country </span><span style="color:gray;">= </span><span style="color:red;">'Denmark'
</span><span style="color:blue;">GO

SELECT </span><span style="color:gray;">* </span><span style="color:blue;">FROM </span><span style="color:teal;">CustomersInDenmark

</span><span style="color:magenta;">UPDATE </span><span style="color:teal;">CustomersInDenmark </span><span style="color:blue;">SET </span><span style="color:teal;">Country </span><span style="color:gray;">= </span><span style="color:red;">'Germany'
</span><span style="color:blue;">GO 

SELECT </span><span style="color:gray;">* </span><span style="color:blue;">FROM </span><span style="color:teal;">CustomersInDenmark
</span><span style="color:blue;">GO
</span>

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

<span style="color:magenta;">UPDATE </span><span style="color:teal;">t1 </span><span style="color:blue;">SET </span><span style="color:teal;">Country </span><span style="color:gray;">= </span><span style="color:red;">'Denmark'
</span><span style="color:blue;">GO

ALTER VIEW </span><span style="color:teal;">CustomersInDenmark </span><span style="color:blue;">AS
SELECT </span><span style="color:gray;">*
</span><span style="color:blue;">FROM </span><span style="color:teal;">t1
</span><span style="color:blue;">WHERE </span><span style="color:teal;">Country </span><span style="color:gray;">= </span><span style="color:red;">'Denmark'
</span><span style="color:blue;">WITH CHECK OPTION
GO

</span><span style="color:magenta;">UPDATE </span><span style="color:teal;">CustomersInDenmark </span><span style="color:blue;">SET </span><span style="color:teal;">Country </span><span style="color:gray;">= </span><span style="color:red;">'Germany'
</span>

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

image

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

<span style="color:magenta;">UPDATE </span><span style="color:teal;">CustomersInDenmark </span><span style="color:blue;">SET </span><span style="color:teal;">CustomName </span><span style="color:gray;">= </span><span style="color:red;">'Bo'
</span>


Any questions?
Please reach out to info@inspari.dk or +45 70 24 56 55 if you have any questions. We are looking forward to hearing from you.