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 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:

[View full width]
 
[View full width]
CREATE VIEW TitleAuthors AS SELECT dbo.Authors.au_id, dbo.Authors.au_lname, dbo.Authors.au_fname, dbo.Authors.phone, graphics/ccc.gif dbo.Authors.contract, dbo.Titles.title, dbo.Titles.type FROM dbo.Authors INNER JOIN dbo.TitleAuthor ON dbo.Authors.au_id = dbo.TitleAuthor. graphics/ccc.gif 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:

 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 book, 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 :

[View full width]
 
[View full width]
CREATE VIEW TitleAuthors AS SELECT dbo.authors.au_id, dbo.authors.au_lname, dbo.authors.au_fname, dbo.authors.phone, graphics/ccc.gif 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 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.

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 .

    graphics/07fig11.gif

  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', 'John', 'Mathew', 10) 
  4. To display the view with the updated record, enter and run the following:

     SELECT *   FROM EmployeeView   WHERE emp_id = 'AEE21349M' 
  5. To see that the record has been added to the base table, Employee , run the following. The results of both of these SELECT statements are shown in Figure 7.12:

     SELECT *   FROM Employee   WHERE emp_id = 'AEE21349M' 
    Figure 7.12. Inserting data through a view.

    graphics/07fig12.jpg

Now that you have seen how to put data into a table through a view, you should also know how to get the data back out of the database through a view.

Deleting Rows Through Views

In the same way data can be retrieved and inserted through a view, it can also be removed. Rows can be removed through a view in almost the same fashion that they can be inserted. In fact, deleting rows from a view is as easy as knowing how to DELETE rows from a regular table.

The biggest rule that can never be broken when you DELETE a row through a view is that the affected row can reference only one table. In other words, the view can be based on only one table. If the view referred to or was based on more than one table, then every row would reference more than one table and could not be deleted. If you attempt to DELETE a row that is based on more than one table, then you receive an error message. Here is an attempted deletion and subsequent error message:

 DELETE FROM TitleAuthors WHERE au_id = '213-46-8915' GO Server: Msg 4405, Level 16, State 1, Line 1 View or function 'TitleAuthors' is not updatable because the modification affects multiple base tables. 

Another requirement for deleting data through a view is that the data specified in the DELETE statement must actually be visible in the view. For example, the following shows the creation of a view, and an attempted data deletion using state as part of the deletion criteria while it is not part of the view. This fails with the specified error.

 CREATE VIEW AuthorsInCA AS   SELECT au_id, au_lname, au_fname     FROM authors     WHERE state = 'CA' GO DELETE FROM AuthorsInCA WHERE au_lname = 'Burns' and state = 'CA' GO Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'state'. 

If the view was created with the state column visible, then the DELETE statement would have succeeded. All components of the DELETE statement must be defined in the view.

The last requirement for deletion of data through a view is that the deletion of the record may not cause any foreign key violations. This type of deletion would not have been allowed outside the view, so it is not allowed through the view either. For example, in the Pubs database, you are not allowed to DELETE an author record if that author is assigned to a book, because the FK_Constraints are declared on the TitleAuthor table with the Authors table.

To DELETE a row through a view, go through the Step by Step 7.6.

STEP BY STEP

7.6 Deleting a Row via a View

  1. Open the Query Analyzer by selecting it from the Start menu.

  2. You will DELETE data through the view you created in the EmployeeView example. To avoid deleting a row that will conflict with the WHERE clause condition, you need to see the definition of the view by running the following:

     Sp_helptext EmployeeView 
  3. Now, DELETE the row you added by running the following code:

     DELETE EmployeeView   WHERE emp_id =  'AEE21349M' 
  4. To make sure the record was deleted, try to query it using the following code. This process is shown in Figure 7.13.

     SELECT *   FROM EmployeeView   WHERE emp_id = 'AEE21349M' 
    Figure 7.13. Deleting data via a view.

    graphics/07fig13.jpg

You will often want to UPDATE data, rather than DELETE it and re-create it. The next section leads you through the requirements for updating data through a view.

Updating Rows Through Views

You can use an UPDATE statement to change one or more rows that a view references. Any changes that are undertaken through the view are actually applied to the underlying table in which the view is defined. The restrictions that applied to INSERT statements used with views also apply to UPDATE statements. These restrictions include the following:

  • 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.

  • Updated data must conform to the view definition when WITH CHECK is specified on the view.

  • Updated data cannot create FOREIGN KEY constraint violations.

When updating a view, you may want to use the WITH CHECK OPTION to force all data modifications executed to be constrained by the view definition. This is important for the same reasons that were important when dealing with inserts: it reduces user confusion with disappearing data, and it enables users to always confirm the accuracy of their data UPDATE .

To UPDATE a record, follow Step by Step 7.7.

STEP BY STEP

7.7 Updating Data Through a View

  1. Open the Query Analyzer by selecting it from the Start menu.

  2. INSERT a record into the view:

     INSERT INTO EmployeeView   (emp_id, fname, lname, job_lvl)   VALUES ('ARP21349M', 'Frandos', 'Fung', 10) 
  3. Assume that you mistook Frandos's last name with another name. To UPDATE the lname column on this record to his real last name, Thatch, run the following query:

     UPDATE EmployeeView   SET lname = 'Thatch'   WHERE emp_id = 'ARP21349M' 
  4. To see the updated record, run the following code. This process is shown in Figure 7.14.

     SELECT *   FROM EmployeeView   WHERE emp_id = 'ARP21349M' 
    Figure 7.14. Updating data through a view.

    graphics/07fig14.jpg

With this discussion of the UPDATE statement, you have now examined all the different options for data modifications: Inserts, Updates, and Deletes, and how these are accomplished through the use of a view. You have seen what can be done, and what cannot be done because of the restrictions that are placed on data modifications through a view. Some of these restrictions are annoying, but most help you develop applications that maintain a higher quality of data.

REVIEW BREAK: Coming to Terms with Views

A view offers several benefits to both a database programmer and a database user. One benefit is ease of use, which comes from the view definition. The view definition may be based on a complex SELECT statement, and the view reduces the definition to a virtual table or record set. The created record set can be used in many areas where normal tables are.

When working with a view definition, use standard commands that would be used with a table: CREATE , to create a view; ALTER , to change the view's definition; and DROP , to delete the view from the database. These commands are similar to the table commands of the same name, and serve the same function. The biggest difference is with the CREATE command, which references columns in a table, rather than storing the definition of each column.

Because the view is based on the SELECT statement, you saw that options that can enhance SELECT statements can also enhance views. These options include aggregates, joins, and views based on views.

The last section that you examined was on data manipulation through a view. These included inserts, updates, and deletes. Performing these tasks is similar to working with a table, but there are some restrictions on what can be done. You can increase these restrictions by using the WITH CHECK OPTION when creating your view. This enforces a requirement that all data modification will continue to be visible through the view.

The next sections examine two more major areas of views. Before attempting to deal with partitioned views, you should be comfortable with the topics covered thus far. If not, review them one more time. The other area that is covered is using views to control data access and ownership chains.



MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
ISBN: N/A
EAN: N/A
Year: 2003
Pages: 228

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net