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.
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.
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.
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:
Before you modify data through a view without using an INSTEAD OF trigger or an updateable partitioned view, consider the following guidelines:
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.
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.
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.
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.
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
INSERT statements add data to the member tables through the partitioned view. The INSERT statements must adhere to the following rules:
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:
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.
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."
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.
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.
A message appears in the Messages tab of the Results pane, stating that the command has been successfully completed.
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.
The result set is displayed in the Grids tab of the Results pane.
USE BookShopDB INSERT AuthorNames VALUES ('William', 'Burroughs')
This statement uses the AuthorNames view to insert a new author.
A message appears in the Messages tab of the Results pane, stating that the command has been successfully completed.
SELECT * FROM Authors
The result set is displayed in the Grids tab of the Results pane.
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.
USE BookShopDB UPDATE AuthorNames SET FirstName = 'John' WHERE LastName = 'Burroughs'
This statement changes the first name of the author whose last name is Burroughs.
A message appears in the Messages tab of the Results pane, stating that the command has been successfully completed.
SELECT * FROM AuthorNames
The result set is displayed in the Grids tab of the Results pane.
Notice that William Burroughs has been changed to John Burroughs.
USE BookShopDB DELETE AuthorNames WHERE LastName = 'Burroughs'
This statement deletes John Burroughs from the Authors table.
A message appears in the Messages tab of the Results pane, stating that the command has been successfully completed.
SELECT * FROM Authors
The result set is displayed in the Grids tab of the Results pane.
Notice that John Burroughs has been deleted from the Authors table.
USE BookShopDB GO DROP VIEW AuthorNames
In this statement, you are deleting the BookAuthorView view from the BookShopDB database.
A message appears in the Messages tab of the Results pane, stating that the command has been successfully completed.
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.