| |
 |
|
SQL Server Tips by Burleson |
With Check Option
This clause is very important because it prevents changes that do
not meet the view's criteria.
Example: Create a view on database pubs for table authors, that
shows the name, phone number and state from all authors from
California. This is very simple:
CREATE VIEW dbo.AuthorsCA
AS
SELECT au_id, au_fname, au_lname, phone, state, contract
FROM dbo.authors
WHERE state = 'ca'
This is an updatable view and a user can change any column, even the
state column:
UPDATE AuthorsCA SET state='NY'
After this update there will be no authors from California. This
might not be the desired behavior.
Example: Same as above but the state column cannot be changed.
CREATE VIEW dbo.AuthorsCA2
AS
SELECT au_id, au_fname, au_lname, phone, state, contract
FROM dbo.authors
WHERE state = 'ca'
With Check Option
The view is still updatable, except for the state column:
UPDATE AuthorsCA2 SET state='NY'
This will cause an error and the state will not be changed.
The above book excerpt is from:
Super SQL
Server Systems
Turbocharge Database Performance with C++ External Procedures
ISBN:
0-9761573-2-2
Joseph Gama, P. J. Naughter
http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm |
