Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SS7
Expertise: Intermediate
Jan 1, 2003

The WITH CHECK OPTION option with views

SQL Server's views are updateable, but it is possible that when you insert a new record or update an existing record, the record added or modified doesn't logical belog to the view any longer. For example, consider the following view:
CREATE VIEW authors_CA AS
    ( 
        SELECT * FROM Authors WHERE state='CA'
    )
If you now issue the following command:
UPDATE authors_CA SET state='NJ'
then all the records visible in the view are updated with a differente State value, and therefore would disappear from the view itself. This is perfectly legal, but can create subtle programming errors. You can avoid this problem by adding the WITH CHECK OPTION predicate when you create the view:
CREATE VIEW authors_CA AS
    ( 
        SELECT * FROM Authors WHERE state='CA'
    )
    WITH CHECK OPTION
Now any insert or update operation that makes a record disappear from the view raises a trappable runtime error.
Francesco Balena
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap