Database Reference
In-Depth Information
8.2.2 Modify Views
When making a change to a view, you can use the ALTER VIEW statement. ALTER VIEW does
not alter the existing indexes, stored procedures, or triggers created for the view. he syntax for the
ALTER VIEW statement is
ALTER VIEW [ db_name .] [ owner .] view_name [( column [,... n ])]
[WITH < view_attribute > [,...n]]
AS
select_statement
[WITH CHECK OPTION]
< view_attribute > :: =
{ENCRYPTION|SCHEMABINDING|VIEW_METADATA}
he deinitions of the keywords in the ALTER VIEW statement are similar to those of the
CREATE VIEW statement.
8.2.3 Modify Data in a View
One of the advantages of a view is that it limits front-end users' direct use and modiication of
data in tables. hrough a view, however, a user may be able to insert, update, and delete data of the
underlying tables according the following rules:
If a view is constructed on a single table and it meets all the restrictions listed below, the
underlying table is updateable (including insert, update, and delete).
For a multiple-table-based view, the data of the underlying tables can only be modiied by
an INSTEAD OF trigger deined on the view.
For a single-table-based view, to make the underlying table updateable, you need to meet the
following restrictions on the SQL statement that creates the view:
If the underlying table contains columns deined as NOT NULL, all the NOT NULL col-
umns should be included in the view.
If the WITH CHECK OPTION clause is used in the CREATE VIEW statement, you can-
not delete a row from the view.
If a view contains calculated columns, the underlying table cannot be modiied through the
view.
When you modify data through a view, the modiication should maintain the data constraints
and integrity constraints imposed on the underlying table. For example, to illustrate the modiica-
tion of data in an underlying table, let us create a view on the STUDENT table, change the last
name Brooks to Parker, and then query the underlying table STUDENT to see the change.
In Figure 8.2, the last name of the student with id 14 is changed to Parker. As mentioned ear-
lier, if a view has a calculated column, you cannot modify data through the view for the underly-
ing table. To illustrate this, consider NEW_STUDENT_VIEW in Figure 8.3.
Note that modifying a view may cause its dependent database objects such as triggers or stored
procedures to become invalid. If this happens, you need to modify its stored procedures or triggers
accord ingly.
Search WWH ::




Custom Search