The purpose of WITH CHECK OPTION is to make sure that your statement causing the data change does not result in any of the rows no longer appearing in the View when the view is referenced.

 

create view vw_Customers_Hertfordshire

with schemabinding

as

select id,customer_name,status,County

from dbo.Customers

where county = ‘Hertfordshire’

with check option

go

The WITH CHECK OPTION only works when you update, insert or delete using the view directly (this is a clue on how it’s been implemented).

An example call would be:

update vw_Customers_Hertfordshire

set status = ‘M’

So, if we try and change the county from ‘Hertfordshire’ to ‘Cumbria’ for instance an error will result…

update vw_Customers_Hertfordshire

set county = ‘Cumbria’

go

Msg 550, Level 16, State 1, Line 1

The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.

The statement has been terminated.

Note that if we just update the base table directly then we get no error…

update Customers

set county = ‘Cumbria’

where county = ‘Hertfordshire’

go

Works fine and updates the 3 rows.

Advertisement