DB2 Top ideas on How to Prevent Updating View

In this post, I have explained the ideas on creating a view which does not allow users to update it. There are rules to make a view non-updatable. Still these ideas helpful for your project.


In simple words, a VIEW is, to the user, a view that just looks like a table. Except for the view definition, a view does not take up space or store its own data; the data presented in a view is derived from other tables.

You can create a view on existing tables, on other views, or some combination of the two. A view defined in another view is called a nested view.

In general, Views are created using SQL, and these are available in any database.

For example DB2, ORACLE, MYSQL, etc. The typical point is the way writing SQL determines, if a view is updatable or not.

SQL Query to create a view

CREATE VIEW NONFICTIONBOOKS
AS SELECT * FROM BOOKS
WHERE BOOKTYPE = 'N';

How to make a VIEW is READ ONLY or updatable-view

When you create a view, due to its structure it may be either a read-only view or an updatable-view.

The SELECT statement of a view determines whether the view is read-only or updatable.

Generally, if the rows of a view can be mapped to rows of the base table, then the view is updatable.

For example, the view NONFICTIONBOOKS, as you defined it in the previous example, is updatable because each row in the view is a row in the base table.

Other Key Rules for Views

The rules for whether a view is updatable are complex and depend on the SELECT statement in the definition.

For example, views that use VALUES, DISTINCT, or any form of join is not directly updatable.

How will you determine a VIEW is updatable or READ-ONLY

You can easily determine whether a view is updatable 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

You can write SQL in the following way:

CREATE VIEW NONFICTIONBOOKS 
AS SELECT * FROM BOOKS 
WHERE BOOKTYPE = 'N' 
WITH CHECK OPTION;

This view still restricts the user from seeing only non-fiction books.

In addition, 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