Database Reference
In-Depth Information
In this case, one view has a set of triggers that prohibits the deletion of a required child and
another view has a set of triggers that deletes a required child as well as the parent. The views
are assigned to different applications, depending on the authority of those applications.
Updating SQL Views
Some views can be updated; others cannot. The rules by which this is determined are both
complicated and dependent on the DBMS in use. To understand why this is so, consider the
following two update requests on views previously defined in our discussion of SQL views:
/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-UPDATE-VIEW-CH07-01 *** */
UPDATE CustomerTableBasicDataView
SET Phone = '543-3456'
WHERE CustomerID = 1000;
and
/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-UPDATE-VIEW-CH07-02 *** */
UPDATE ArtistWorkTotalNetView
SET TotalNetProfit = 23000
WHERE ArtistLastName = 'Tobey';
The first request can be processed without problem because Customer-
TableBasicDataView is just an alias for the CUSTOMER table. The second update, however,
makes no sense at all. TotalNetProfit is a sum of a computed column. Nowhere in the actual
tables in the database is there any such column to be updated.
Figure 7-19 shows general guidelines to determine if a view is updatable. Again, the
specifics depend on the DBMS product in use. In general, the DBMS must be able to associate
the column(s) to be updated with a particular row in a particular table. A way to approach
this question is to ask yourself, “What would I do if I were the DBMS and I were asked to
update this view? Would the request make sense, and, if so, do I have sufficient data to make
the update?” Clearly, if the entire table is present and there are no computed columns, the
view is updatable. Also, the DBMS will mark the view as updatable if it has an INSTEAD OF
trigger defined for it, as described later.
However, if any of the required columns are missing, the view clearly cannot be used for
inserts. It may be used for updates and deletes, however, as long as the primary key (or, for
Figure 7-19
Guidelines for Updating SQL
Views
Updatable Views
View based on a single table with no computed columns and all
non-null columns present in the view.
View based on any number of tables, with or without computed
columns, and INSTEAD OF trigger defined for the view.
Possibly Updatable Views
Based on a single table, primary key in view, some required
columns missing from view, update and delete may be allowed.
Insert is not allowed.
Based on multiple tables, updates may be allowed on the most
subordinate table in the view if rows of that table can be uniquely
identified.
Search WWH ::
Custom Search