Web Hosting Java Blog

418 Part I EXAM PREPARATION STEP BY STEP 7.5 Adding Data Through a View 1. Open the Query Analyzer by selecting it from the Start menu. 2. You are going to add data through EmployeeView, which was the view you created in Step by Step 7.4. To avoid adding a row that will conflict with the WHERE clause condition, you need to see the definition of the view. Use sp_helptext to display the definition of the view, as shown in Figure 7.11: Sp_helptext EmployeeView FIGURE 7.11 The definition of EmployeeView. 3. Now, INSERT a record into the base table, making sure the new record does not conflict with the condition specified in the WHERE clause of the view definition: INSERT INTO EmployeeView (emp_id, fname, lname, job_lvl) VALUES ( AEE21349M ,

For high quality website hosting services please check java web hosting website.

Chapter 7 WORKING WITH VIEWS 417 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. Because this INSERT results in data that was not visible through the view, it is not allowed. This may be advantageous for some databases if you want to restrict what values are inserted into the view. In this particular case, it prevents the addition of both Authors and Titles, because you can t use a single statement to INSERT into both tables, and either INSERT creates data that is not visible through the view. Data updates, other than inserts, are still allowed, as long as the resulting data is still visible through the view. Enabling users to add data to a view that will not then be visible leaves two problems. After the INSERT has been carried out, the data will not be visible, which causes some users to attempt additional updates (thinking the previous one failed). If the users are aware that the data INSERT did succeed, they are still unable to check the accuracy of the UPDATE, because the data is not visible. You should consider using the WITH CHECK OPTION with all views to limit data inserts and avoid user confusion. To add data through a view, follow Step by Step 7.5.

For high quality website hosting services please check java web hosting website.

416 Part I EXAM PREPARATION INSERT INTO TitleAuthors (au_id, au_lname, au_fname, phone, contract) VALUES ( 212-55-1212 , Burns , Bobby , 212 555-1212 , 1) After this, however, the Bobby Burns record will not be visible to the view. To then associate the new (non-visible) author with a blog, you have to UPDATE the TitleAuthor table, which cannot be done through the view, because those columns are not present in the view. References to the TitleAuthor table are used to create the view, but are not part of the column structure in the view. To properly satisfy the INSERT requirement for the Author table, the previous UPDATE to the TitleAuthors view had to UPDATE at least the au_id, au_lname, au_fname, phone, and contract columns because these columns are all set to not allow NULL values. The only piece of information that could have been left out of the INSERT would have been the phone column, because that defaults to ( UNKNOWN ). If you examine the view definition that was used in the TitleAuthor view, you will notice that it is a result of an INNER JOIN between three tables. This implements a restriction on the visible data. Other restrictions on the visible data may be the result of the WHERE clause. In the previous Bobby Burns INSERT, you were able to INSERT data that was then no longer visible to the view. You can confirm that Bobby Burns is in the Authors table by using SELECT * FROM Authors WHERE au_lname = Burns . The current view definition enables you to INSERT data that is not actually visible through the view. If you want to prevent this from happening, you can use the WITH CHECK OPTION when creating your view. Here is an example of the TitleAuthors view using the WITH CHECK OPTION: CREATE VIEW TitleAuthors AS SELECT dbo.authors.au_id, dbo.authors.au_lname, .dbo.authors.au_fname, dbo.authors.phone, .dbo.authors.contract, dbo.titles.title, .dbo.titles.type FROM dbo.authors INNER JOIN dbo.titleauthor ON dbo.authors.au_id = .dbo.titleauthor.au_id INNER JOIN dbo.titles ON dbo.titleauthor.title_id = .dbo.titles.title_id WITH CHECK OPTION Now if a similar data INSERT is attempted, you will receive the following error message: 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

For high quality java hosting services please check java web hosting website.

Chapter 7 similar to adding data directly to a normal table. Therefore, you can still use the INSERT statement in the same way. After you ve created the view, you reference the view in an INSERT statement to add rows just as if you ve referenced a table in the INSERT statement. Adding data through a view is as easy as adding data to a regular table. However, you must adhere to a number of conditions. The most important of these conditions are the following: INSERT and UPDATE statements must modify only one of the underlying tables at a time. If you want to UPDATE data that resides in more than one table, you have to perform the UPDATE in two or more statements. Inserts against the underlying table must provide values for all NOT NULL columns, unless DEFAULT values are declared for those columns. Inserted data must conform to the view definition when WITH CHECK is specified on the view. If you are not sure whether your INSERT will be valid, you may want to use sp_helptext to check the SELECT statement that creates the view. A view is not a table, even though it an often be treated like one. If a view displays columns from only one table, then you do not have to worry about inserts and updates affecting more than one table. If, however, your view is used to consolidate data from many tables, you have to be careful when you UPDATE columns in more than one table. When you INSERT data through the view, you may find that the view actually makes the process difficult. For example, if you create the following view in the Pubs database: CREATE VIEW TitleAuthors AS SELECT dbo.Authors.au_id, dbo.Authors.au_lname, .dbo.Authors.au_fname, dbo.Authors.phone, .dbo.Authors.contract, dbo.Titles.title, .dbo.Titles.type FROM dbo.Authors INNER JOIN .dbo.TitleAuthor ON dbo.Authors.au_id = .dbo.TitleAuthor.au_id INNER JOIN .dbo.Titles ON dbo.TitleAuthor.title_id = dbo.Titles.title_id You can then add an author record with the following statement: WORKING WITH VIEWS 415

For high quality java hosting services please check tomcat web hosting website.

414 Part I EXAM PREPARATION GO DROP VIEW auth1 GO SELECT * FROM auth2 GO When this code is executed, you get the following error message: Server: Msg 208, Level 16, State 1, Procedure auth2, Line 3 Invalid object name pubs.dbo.auth1 . Server: Msg 4413, Level 16, State 1, Line 1 Could not use view or function auth2 because of binding errors. This error message lets you know that the dependent view pubs.dbo.auth1 is not present in the database. MODIFYING DATA THROUGH VIEWS . Manage data manipulation by using views. Not only can you retrieve data by using a view, you can also modify the data. Modification includes all inserts, deletes, and updates. When you modify, delete, and update data, the data definition in the view does not change; the data modification is aimed at the underlying referenced tables associated with the view. A view does not in any way lose its definition; therefore it is not affected in any way when modification queries are executed. You can easily modify the data through a view, as long as the view has been based on at least one table, and it does not SELECT aggregate functions. In addition, if you need to perform additional data modification through the view, you can use the INSTEAD OF triggers. INSTEAD OF triggers exist for UPDATE, DELETE, and INSERT statements. For the time being, we ll stick with modifying data through a view without the help of the INSTEAD OF trigger, which is covered in greater detail in Chapter 8, Triggers. Inserting Rows Through Views In addition to retrieving rows, you can use views to insert data into an underlying base table. Adding data through a view is extremely

For high quality jboss hosting services please check jboss web hosting website.

Chapter 7 WORKING WITH VIEWS 413 .AuthorShortInfo, Line 2 Invalid column name city . Server: Msg 4413, Level 16, State 1, Line 1 Could not use view or function AuthorShortInfo because of binding errors. This error states that a required object (the city column) no longer exists. This problem is possible, and can easily occur, because of the lack of checking on changes to source objects. This same error message would result if you execute sp_refreshview on AuthorShortInfo, and sp_refreshview would return a non-zero value because of the failure. It is possible to create a small procedure to query sysobjects for views, loop them through them, and refresh each view to ensure that they are all still valid. Dropping Views To remove a view from a database, use the DROP VIEW statement. Dropping a view removes the definition of the view from the database and an entry in the sysobjects while not affecting the underlying tables and views. The DROP VIEW command is relatively simple as shown here: DROP VIEW { view } [ ,…n ] To drop a view named my_view, you simply have to execute DROP VIEW my_view . With the DROP VIEW command you may also choose to remove a number of views at the same time. To do this, specify each view in a comma-delimited list. Dropping an indexed view leads to the removal of all indexes associated with that view. To drop the view created in Step by Step 7.4, enter and run the following code: DROP VIEW EmployeeView GO If you drop a view that is used by another object, such as a view, you cause problems with the dependent view. For example, examine the following code: CREATE VIEW auth1 AS SELECT au_fname, au_lname, city, state FROM pubs.dbo.authors GO CREATE VIEW auth2 AS SELECT au_fname, au_lname FROM pubs.dbo.auth1 WARNING Dropping Used Views Dropping views that are used by other views causes the dependent views to stop working, because the object that they refer to no longer exists.

For high quality website hosting services please check cheap web hosting website.

412 Part I EXAM PREPARATION ALTER VIEW EmployeeView AS SELECT emp_id, fname, lname, job_lvl FROM Employee WHERE job_lvl <150 You may find that SQL Server does not allow you to perform tasks because of one dependency or another. For instance, all objects are dependent on their owner, so you are not able to drop a user from the database if they own objects. The number of database objects that the server checks for dependencies is very large, but it is easy to find out what objects they are. A quick query of sysobjects can reveal any object ownership dependencies. Scripts, stored procedures, and views can exist anywhere, and refer to tables or views in your database. There is no easy location that can be checked that tells SQL Server who is referencing your table or view. Another user can reference your view from an entirely different server, without your knowledge, if that user has been granted the SELECT permission to it. Therefore, there is no back-checking of integrity when you change the structure of tables or views who knows who might be using it? When examining the situation from this perspective, it is easy to see why SQL Server does not check; but it is still surprising that it does not check. Take for example the following script: USE pubs GO CREATE VIEW AuthorInfo AS SELECT au_id, au_fname, au_lname, city, zip FROM dbo.Authors GO CREATE VIEW AuthorShortInfo AS SELECT au_id, au_fname, city FROM dbo.AuthorInfo GO ALTER VIEW AuthorInfo AS SELECT au_id, au_fname, au_lname, zip FROM dbo.Authors GO SELECT * FROM AuthorShortInfo Running this script would yield the following error messages in Query Analyzer: Server: Msg 207, Level 16, State 3, Procedure

For high quality website hosting services please check java web hosting website.

Chapter 7 WORKING WITH VIEWS 411 the definition of a view without affecting permissions granted. The syntax for ALTER VIEW is as follows: ALTER VIEW [ < database_name > . ] [ < owner > . ] .view_name [ ( column [ ,…n ] ) ] [ WITH < view_attribute > [ ,…n ] ] AS < select_statement > [ WITH CHECK OPTION ] < view_attribute > ::= { ENCRYPTION | SCHEMABINDING | VIEW_METADATA } The syntax is relatively similar to CREATE VIEW. view_name is the name of the view being altered. The WITH ENCRYPTION clause protects the definition of your view. You encrypt the definition of your view because you may not want users to display it, to protect your design WARNING Don t Be Careless with Changes from duplication. Encrypting your view using WITH ENCRYPTION Altering views or tables that are ensures that no one can display your view, whether using sp_helptext, used by views may cause the viewing it through the Enterprise Manager, or generating it through dependent views to stop working. a Database Creation Script. SQL Server does not back-check all table or view alterations to If your original view was created with WITH ENCRYPTION or WITH ensure they do not create errors. SCHEMABINDING, then your ALTER VIEW statement must include the This is where the SCHEMABINDING same options. To alter a view using ALTER VIEW, see Step by Step 7.4 option helps out. If you have SCHEMA-bound objects, then you cannot ALTER the source objects at all. It may be preferable to drop and recreate a series of STEP BY STEP 7.4 Altering a View Using the ALTER VIEW objects, rather than have objects Statement that do not function. This means more planning. 1. Open the SQL Server Query Analyzer by selecting it from the Start menu. 2. Before altering the definition of a view, there must already be an existing view. Run the following query to create a new view for the purposes of this example: USE pubs GO CREATE VIEW EmployeeView AS SELECT emp_id, fname, lname, job_lvl FROM Employee WHERE fname LIKE p% 3. To change the definition of the view you created in Step 2, you need the help of the ALTER VIEW statement. Run the following query:

For high quality java hosting services please check java web hosting website.

410 Part I EXAM PREPARATION Altering Views NOTE SQL Server accommodates the need to adjust a view, whether it is renaming or completely changing a view definition. This flexibility is provided via the sp_rename stored procedure and the ALTER VIEW statement. The following sections describe these two in greater depth. The Missing OBJECT In most cases, you can omit the @objtype if FIGURE 7.10 it is going to be OBJECT . The Create View Wizard is a simple five-step wizard. Renaming a View Renaming a view, or any object for that matter, is done with the sp_rename system stored procedure. sp_rename [ @objname = ] object_name , [ @newname = ] new_name [ , [ @objtype = ] object_type ] To use this procedure, first supply the original name followed by a comma and then type in the new name of the view. Because this system stored procedure is extremely generic and used for a lot of objects, you have to specifically specify OBJECT as object_type. Views, triggers, constraints, and stored procedures all belong in the category OBJECT. Renaming views and stored procedures causes the sysobjects system table to be updated. For example, to rename a view in the Pubs database you could use: use pubs GO sp_rename titleview , oldtitleview , OBJECT Use a great deal of care when renaming objects because any scripts, stored procedures, or objects that refer to the renamed object by its old name will fail to work. Editing a View You may need to change the way a view accesses data, known as the view definition. When doing this, there must already be a previously created view using only the CREATE VIEW statement. You can also drop and re-create the view, but doing so resets previously granted permissions. Using the ALTER VIEW statement, you can easily reshape

If you looking for unlimited one inclusive web hosting plan please check web hosting plan website.

410 Part I EXAM PREPARATION Altering Views NOTE SQL Server accommodates the need to adjust a view, whether it is renaming or completely changing a view definition. This flexibility is provided via the sp_rename stored procedure and the ALTER VIEW statement. The following sections describe these two in greater depth. The Missing OBJECT In most cases, you can omit the @objtype if FIGURE 7.10 it is going to be OBJECT . The Create View Wizard is a simple five-step wizard. Renaming a View Renaming a view, or any object for that matter, is done with the sp_rename system stored procedure. sp_rename [ @objname = ] object_name , [ @newname = ] new_name [ , [ @objtype = ] object_type ] To use this procedure, first supply the original name followed by a comma and then type in the new name of the view. Because this system stored procedure is extremely generic and used for a lot of objects, you have to specifically specify OBJECT as object_type. Views, triggers, constraints, and stored procedures all belong in the category OBJECT. Renaming views and stored procedures causes the sysobjects system table to be updated. For example, to rename a view in the Pubs database you could use: use pubs GO sp_rename titleview , oldtitleview , OBJECT Use a great deal of care when renaming objects because any scripts, stored procedures, or objects that refer to the renamed object by its old name will fail to work. Editing a View You may need to change the way a view accesses data, known as the view definition. When doing this, there must already be a previously created view using only the CREATE VIEW statement. You can also drop and re-create the view, but doing so resets previously granted permissions. Using the ALTER VIEW statement, you can easily reshape

For reliable and cheap web hosting services please check tomcat web hosting website.