Hướng dẫn học Microsoft SQL Server 2008 part 39

hông được kiểm soát dữ liệu Để hiểu được sự cần thiết của VỚI CHECK OPTION, điều quan trọng là đầu tiên hiểu thế nào mà không có chức năng xem CHECK OPTION. Quan điểm sau đây sẽ tạo ra một danh sách các tour du lịch cho các trại Cape Hatteras cơ sở: | Part II Manipulating Data with Select Unchecked data To understand the need for the WITH CHECK OPTION it s important to first understand how views function without the CHECK OPTION. The following view will generate a list of tours for the Cape Hatteras base camp USE CHA2 CREATE VIEW AS SELECT TourName BaseCampID FROM WHERE BaseCampID 2 go SELECT TourName BaseCampID FROM TourName BaseCampID Outer Banks Lighthouses 2 If the Ashville base camp adds a Blue Ridge Parkway Hike tour and inserts it through the view without the CHECK OPTION the INSERT is permitted INSERT TourName BaseCampID VALUES Blue Ridge Parkway Hike 1 1 row s affected The INSERT worked and the new row is in the database but the row is not visible through the view because the WHERE clause of the view filters out the inserted row. This phenomenon is called disappearing rows SELECT TourName BaseCampID FROM TourName BaseCampID Outer Banks Lighthouses 2 If the purpose of the view were to give users at the Cape access to their tours alone then the view failed. Although they can see only the Cape s tours they successfully modified another base camp s tours. The WITH CHECK OPTION would have prevented this fault. Protecting the data A view with a WHERE clause and the WITH CHECK OPTION can protect the data from undesired inserts and updates. The following code will back out the previous INSERT and redo the same scenario but this time the view will include the WITH CHECK OPTION DELETE WHERE TourName Blue Ridge Parkway Hike 342 Projecting Data Through Views 14 go ALTER VIEW AS SELECT TourName BaseCampID FROM WHERE BaseCampID 2 WITH CHECK OPTION go INSERT TourName BaseCampID VALUES Blue Ridge Parkway Hike 1 Server Msg 550 Level 16 State 1 Line 1 The attempted insert or update failed because the target view either specifies .