Lesson 3:Accessing Data through Views

3 4

Once a view has been created, you can use the view to access the data that is returned by the SELECT query defined within that view. Most operations that you can perform on a query can be performed on a view. In this lesson, you will learn how to use views to view the data returned by the SELECT query, and you will learn how to modify that data.


After this lesson, you will be able to:

  • Use a view to view data.
  • Use a view to insert, update, and delete data.

Estimated lesson time: 35 minutes


Viewing Data through Views

There are no restrictions on querying data through views. You can use a view in a SELECT statement to return data in much the same way as you use a table. The following SELECT statement enables you to view data returned by the CustomerOrders view in the Northwind database:

 USE Northwind SELECT *  FROM CustomerOrders 

As with tables, you can be more specific about the type of data you return in a view. The following SELECT statement retrieves the OrderID column and the OrderDate column for any orders made for the QUICK-Stop company:

 USE Northwind SELECT OrderID, OrderDate FROM CustomerOrders WHERE CompanyName = 'quick-stop' ORDER BY OrderID 

In this statement, you are also specifying for the result set to be ordered by the OrderID values.

Modifying Data through Views

Views in all versions of SQL Server are updateable (can be the target of UPDATE, DELETE, or INSERT statements) as long as the modification affects only one of the base tables referenced by the view. SQL Server 2000 supports more complex types of INSERT, UPDATE, and DELETE statements that reference views. For example, INSTEAD OF triggers can be defined on a view to specify the individual updates that must be performed against the base tables to support the INSERT, UPDATE, or DELETE statement. Also, partitioned views support INSERT, UDPATE, and DELETE statements that modify multiple member tables referenced by the view.

Modifying Data through Basic Views

There are few restrictions on modifying data through a view. However, if a view does not use an INSTEAD OF trigger or is not an updateable partitioned view, it can still be updateable, provided it meets the following conditions:

  • The view contains at least one table in the FROM clause of the view definition. The view cannot be based solely on an expression.
  • No aggregate functions (AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, and VARP) or GROUP BY, UNION, DISTINCT, or TOP clauses are used in the select list. Aggregate functions, however, can be used within a subquery defined in the FROM clause, provided that the derived values generated by the aggregate functions are not modified.
  • No derived columns are used in the select list. Derived columns are result set columns formed by anything other than a simple column reference.

Before you modify data through a view without using an INSTEAD OF trigger or an updateable partitioned view, consider the following guidelines:

  • All data modification statements executed against the view must adhere to the criteria set within the SELECT statement defining the view if the WITH CHECK OPTION clause is used in the definition of the view. If the WITH CHECK OPTION clause is used, rows cannot be modified in a way that causes them to disappear from the view. Any modification that would cause this situation to happen is canceled, and an error is displayed.
  • SQL Server must be capable of unambiguously resolving the modification operation to specific rows in one of the base tables referenced by the view. You cannot use data modification statements on more than one underlying table in a single statement. Therefore, the columns listed in the UPDATE or INSERT statement must belong to a single base table within the view definition.
  • All of the columns in the underlying table that are being updated and that do not allow null values have values specified in either the INSERT statement or DEFAULT definitions. This feature ensures that all of the columns in the underlying table that require values have them.
  • The modified data in the columns in the underlying table must adhere to the restrictions on those columns, such as nullability, constraints, DEFAULT definitions, and so on. For example, if a row is deleted, all of the underlying FOREIGN KEY constraints in related tables must still be satisfied for the delete to succeed.
  • A distributed partition view (remote view) cannot be updated by using a keyset-driven cursor. This restriction can be resolved by declaring the cursor on the underlying tables and not on the view itself.

Additionally, to delete data in a view, only one table can be listed in the FROM clause of the view definition. The READTEXT and WRITETEXT statements cannot be used with text, ntext, or image columns in a view.

Adding Data through a View

Suppose you create a view in the Northwind database that returns the customer ID and the company name from the Customers table:

 USE Northwind GO CREATE VIEW CustomerView AS SELECT CustomerID, CompanyName FROM Customers 

To insert data through the CustomerView view, you should use the INSERT statement (as shown in the following example):

 USE Northwind INSERT CustomerView VALUES ('TEST1', 'Test Company') 

This statement inserts a new row into the Customers table. Because all other columns in the table permit null values, you do not have to specify a value for those columns.

Changing Data through a View

To modify data through a view, you should use the UPDATE statement (as shown in the following example):

 USE Northwind UPDATE CustomerView SET CustomerID = 'TEST2' WHERE CustomerID = 'TEST1' 

This statement changes the customer ID of Test Company to TEST2.

Deleting Data through a View

To delete data through a view, you should use the DELETE statement, as shown in the following example:

 USE Northwind DELETE CustomerView WHERE CustomerID = 'TEST2' 

This statement deletes the Test Company row from the Customers table.

Modifying Data through Partitioned Views

If a local or distributed partitioned view is not updateable, it can serve only as a read-only copy of the original table. An updateable partitioned view can exhibit all of the capabilities of the original table.

A view is considered an updateable partitioned view if it is defined with a set of SELECT statements whose individual result sets are combined into one by using the UNION ALL statement. Each individual SELECT statement references one SQL Server base table. The table can be either a local table or a linked table referenced using a four-part name, the OPENROWSET function, or the OPENDATASOURCE function (you cannot use an OPENDATASOURCE or OPENROWSET function that specifies a pass-through query).

In addition to the rules defined for updateable partitioned views, data modification statements referencing the view must adhere to the rules defined for INSERT, UPDATE, and DELETE statements.

NOTE


You can modify data through a partitioned view only if you install Microsoft SQL Server 2000 Enterprise Edition or Microsoft SQL Server 2000 Developer Edition.

INSERT Statements

INSERT statements add data to the member tables through the partitioned view. The INSERT statements must adhere to the following rules:

  • All columns must be included in the INSERT statement even if the column can be NULL in the base table or has a DEFAULT constraint defined in the base table.
  • The DEFAULT keyword cannot be specified in the VALUES clause of the INSERT statement.
  • INSERT statements must supply a value that satisfies the logic of the CHECK constraint defined on the partitioning column for one of the member tables.
  • INSERT statements are not allowed if a member table contains a column that has an identity property.
  • INSERT statements are not allowed if a member table contains a timestamp column.
  • INSERT statements are not allowed if there is a self-join with the same view or with any of the member tables.

UPDATE Statements

UPDATE statements modify data in one or more of the member tables through the partitioned view. The UPDATE statements must adhere to the following rules:

  • UPDATE statements cannot specify the DEFAULT keyword as a value in the SET clause, even if the column has a DEFAULT value defined in the corresponding member table.
  • The value of a column with an identity property cannot be changed; however, the other columns can be updated.
  • The value of a PRIMARY KEY cannot be changed if the column contains text, image, or ntext data.
  • Updates are not allowed if a base table contains a timestamp column.
  • Updates are not allowed if there is a self-join with the same view or any of the member tables.
  • The DEFAULT keyword cannot be specified in the SET clause of the UPDATE statement.

DELETE Statements

DELETE statements remove data in one or more of the member tables through the partitioned view. The DELETE statements are not allowed if there is a self-join with the same view or any of the member tables.

Modifying Data when the INSTEAD OF Trigger Is Used

INSTEAD OF triggers override the standard actions of the triggering statement (INSERT, UPDATE, or DELETE). For example, an INSTEAD OF trigger can be defined to perform error or value checking on one or more columns and then to perform additional actions before inserting the record. For instance, when the value being updated in an hourly wage column in a payroll table exceeds a specified value, a trigger can be defined to either produce an error message and roll back the transaction or insert a new record into an audit log before inserting the record into the payroll table.

INSTEAD OF triggers can be defined on either tables or views; however, INSTEAD OF triggers are most useful for extending the types of updates that a view can support. INSTEAD OF triggers are discussed in more detail in Chapter 9, "Implementing Triggers."

Exercise 2:  Using the AuthorsBooks View to Access Data

In this exercise, you will create a view on the Authors table that includes only the first and last names of the authors. You will then use this view to add an author to the database, modify that author, and then delete the author. To perform this exercise, you should be logged into your Windows 2000 Server computer as Administrator.

To create the AuthorNames view to view data

  1. Open Query Analyzer and connect to your local server.
  2. In the Editor pane of the Query window, enter the following Transact-SQL code:
 USE BookShopDB GO CREATE VIEW AuthorNames AS SELECT FirstName, LastName FROM Authors 

In this statement, you are creating a view that returns the first and last names of the authors listed in the Authors table of the BookShopDB database.

  1. Execute the Transact-SQL statement.

A message appears in the Messages tab of the Results pane, stating that the command has been successfully completed.

To use the AuthorNames view to view data

  1. In the Editor pane of the Query window, enter the following Transact-SQL code:
 USE BookShopDB SELECT * FROM AuthorNames ORDER BY LastName 

This statement uses the AuthorNames view to retrieve data. The result set is ordered by the last names of the authors.

  1. Execute the Transact-SQL statement.

The result set is displayed in the Grids tab of the Results pane.

To use the AuthorNames view to insert data

  1. In the Editor pane of the Query window, enter the following Transact-SQL code:
 USE BookShopDB INSERT AuthorNames VALUES ('William', 'Burroughs') 

This statement uses the AuthorNames view to insert a new author.

  1. Execute the Transact-SQL statement.

A message appears in the Messages tab of the Results pane, stating that the command has been successfully completed.

  1. In the Editor pane of the Query window, enter and execute the following Transact-SQL code:
 SELECT * FROM Authors 

The result set is displayed in the Grids tab of the Results pane.

  1. Scroll to the last row of the result set.

Notice that William Burroughs has been added to the list of authors. Also notice that the YearBorn, YearDied, and Description columns have been assigned the default value of N/A.

To use the AuthorNames view to modify data

  1. In the Editor pane of the Query window, enter the following Transact-SQL code:
 USE BookShopDB UPDATE AuthorNames SET FirstName = 'John' WHERE LastName = 'Burroughs' 

This statement changes the first name of the author whose last name is Burroughs.

  1. Execute the Transact-SQL statement.

A message appears in the Messages tab of the Results pane, stating that the command has been successfully completed.

  1. In the Editor pane of the Query window, enter and execute the following Transact-SQL code:
 SELECT * FROM AuthorNames 

The result set is displayed in the Grids tab of the Results pane.

  1. Scroll to the last row of the result set.

Notice that William Burroughs has been changed to John Burroughs.

To use the AuthorNames view to delete data

  1. In the Editor pane of the Query window, enter the following Transact-SQL code:
 USE BookShopDB DELETE AuthorNames WHERE LastName = 'Burroughs' 

This statement deletes John Burroughs from the Authors table.

  1. Execute the Transact-SQL statement.

A message appears in the Messages tab of the Results pane, stating that the command has been successfully completed.

  1. In the Editor pane of the Query window, enter and execute the following Transact-SQL code:
 SELECT * FROM Authors 

The result set is displayed in the Grids tab of the Results pane.

  1. Scroll to the last row of the result set.

Notice that John Burroughs has been deleted from the Authors table.

To delete the AuthorNames view in the BookShopDB database

  1. In the Editor pane of the Query window, enter the following Transact-SQL code:
 USE BookShopDB GO DROP VIEW AuthorNames 

In this statement, you are deleting the BookAuthorView view from the BookShopDB database.

  1. Execute the Transact-SQL statement.

A message appears in the Messages tab of the Results pane, stating that the command has been successfully completed.

Lesson Summary

Once a view has been created, you can use the view to access the data that is returned by the SELECT query defined within that view. You can use a view in a SELECT statement to return data in much the same way as you use a table. SQL Server also enables you to use views to modify data. In addition to modifying data through basic views, you can modify data through updateable partitioned views and views that use INSTEAD OF triggers. If a view does not use an INSTEAD OF trigger or is not an updateable partitioned view, it can still be updateable provided it meets specific conditions. If the conditions are met, you can insert, update, or delete data through the view. A view is considered an updateable partitioned view if the view is a set of SELECT statements whose individual result sets are combined into one by using the UNION ALL statement. In addition to the rules defined for updateable partitioned views, data modification statements referencing the view must adhere to the rules defined for INSERT, UPDATE, and DELETE statements. INSTEAD OF triggers override the standard actions of the triggering statement (INSERT, UPDATE, or DELETE). INSTEAD OF triggers can be defined for either tables or views; however, INSTEAD OF triggers are most useful for extending the types of updates that a view can support.



Microsoft Press Staff - MCSE. Microsoft SQL Server 2000 Database Design and Implementation Training Kit
MCSE Training Kit (Exam 70-229): Microsoft SQL Server(TM) 2000 Database Design and Implementation (Pro Certification)
ISBN: 073561248X
EAN: 2147483647
Year: 2001
Pages: 97

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