A View looks like a table – logical in nature. It does not store data on its own stand. The data you see in a view is come from the base table.
In three ways, you can create views – On existing tables, on Views, or some combination of the two. Defining View from another View is called Nested View. You need SQL skills to create a view, and you can find these in any database. The databases are DB2, ORACLE, and MYSQL. If a View is updatable or not depends on the View definition.
How to Create a VIEW
CREATE VIEW NONFICTIONBOOKS
AS SELECT * FROM BOOKS
WHERE BOOKTYPE = 'N';
How to Create Read-only or Updatable View
- View structure decides if it is read-only or updatable
- The SELECT statement of a view determines View is read-only or updatable.
- If the rows of a view can be mapped to rows of the base table, then the view is updatable.
- The view NONFICTIONBOOKS defined above is updatable because each row in the view is a row in the base table.
Rules to Create a View
- It depends on the SELECT statement definition.
- Views that use VALUES, DISTINCT, or any form of join is not updatable.
How to Get Information from Catalog Tables
You can get information about view is updatable or not by looking at the READONLY column of the VIEWS system-catalog table: Y means it is read-only and N means it is not.
How to Prevent Updating a View
Below is the sample SQL View create Query:
CREATE VIEW NONFICTIONBOOKS
AS SELECT * FROM BOOKS
WHERE BOOKTYPE = 'N'
WITH CHECK OPTION;
It restricts the user from seeing only non-fiction books. Also, it prevents the user from inserting rows that do not have a value of “N” in the BOOKTYPE column and updating the value of the BOOKTYPE column in existing rows to a value other than N.
How to UPDATE Read-only View
- There is a mechanism to allow the appearance of updating data through a read-only view: INSTEAD OF triggers.
- These triggers can be defined on a view to intercept UPDATE, INSERT, and DELETE against a view, and instead perform actions against other tables, most commonly the base tables the view is built upon.
Related Posts
Srinimf 