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
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.
Relaterede Posts
Output row values effected by an update
Have you ever thought about logging the before and after value for an update or delete? It’s...
Most wanted new features in tableau 10
Om det er fedt at dyrke features i software? Nope, men vi gør alligevel en undtagelse. Ganske...
Inspari BI Tool Update - Qlik Sense, Tableau & Power BI
Nogle har en forkærlighed for iOS, mens andre sværger til Android eller Windows. Den form for...