Chapter 9 Suggested Practice 1

This chapter dealt with Views, Inline Functions, and Synonyms

The first practice asks you to create a view with CHECK OPTION, then try to insert something into the view, but because the view was designed with CHECK OPTION it fails and is terminated.  Take the CHECK OPTION off and it works.

So just to be clear, I think of a view a lot like a CTE that you can always access.  There are a few options with a view though such as with schemabinding, with encryption, with metadata, and with check option.  It might not matter, but something to remember would be that you have to specify the "with check option" after the "where" clause in the Select statement.

So this is the code that I used in this practice:

IF OBJECT_ID(N'chnine','V') IS NOT NULL DROP VIEW chnine

GO

CREATE VIEW chnine

AS

SELECT * FROM hr.Employees

WHERE country = 'USA'

WITH CHECK OPTION;

--

INSERT INTO dbo.chnine (lastname,firstname,title,titleofcourtesy,birthdate,hiredate,

address,city, region, postalcode, country, phone) VALUES('smith','jim','ceo','mr.',

'19980216','20080201','1234elm','Dallas','TX','23201','Canada','2065511253')

--

SELECT * FROM chnine

--

DELETE FROM chnine

WHERE lastname = 'smith'

--

You shouldn't run all of this code at once, but highlight it and run it separately.  I'm working on the second piece of this chapter now, will post results for the Synonyms project next!

Cheers!