Creating a view using With Check Option will restrict the queries to only those rows directly visible by the view.
http://www.sqlteam.com/FORUMS/topic.asp?TOPIC_ID=66019
It prevents row from dissappearing from the view implementing this option.
drop table T100
go
drop view VT100
go
Create table T100 (A int)
GO
Create view VT100 AS (SELECT * FROM T100 WHERE A < 2) with check option
GO
INSERT INTO T100 VALUES (1) — Part of the view’s result
INSERT INTO T100 VALUES (2)
GO
SELECT * FROM VT100 — SHOWS 1
GO
INSERT INTO VT100 VALUES (-2) — Works
GO
UPDATE VT100 SET A=5 — Update fails because of WITH CHECK OPTION – GOOD
GO
INSERT INTO VT100 VALUES (999) — Inser fails because of WITH CHECK OPTION – GOOD
GO
DELETE FROM VT100 — DELETES ALL ROWS!!!! ******************* ???
go
print ‘Select from T100’
SELECT * FROM T100 — Still has one row
Results:
(1 row(s) affected)
(1 row(s) affected) A ----------- 1
(1 row(s) affected) (1 row(s) affected) Server: 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. Server: 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.
(2 row(s) affected) Select from T100 A ----------- 2
(1 row(s) affected)