User Defined Views – Part 4

 

Most information technology professionals do not know that data can be modified thru a VIEW. The insert, update, and delete statements, Data Manipulation Language (DML), can be executed on a view as long as the following set of rules are adhered to.

 

  1. Modifications must reference columns from only one base table.
  2. The columns must directly reference the underlying data table.

Today, I am going to explore how to modify data in a view using the [AUTOS] sample database. Enclosed is the complete TSQL for this article including the data definition language (DDL) statements to create this database.

The entity relationship diagram (ERD) below shows the two tables and two views we are going to work with today.

 

The first view that I am going to create inner joins the [MAKES] table to the [MODELS] table including all columns.

The data in this view can be successfully queried using the SELECT statement below.

Executing UPDATE statements that reference only one table at a time in the view are successful since they abide by the rules.

Performing an UPDATE statement that references both tables in the view fails since it breaks the rules.

Let’s add the electronic car maker named ‘Tesla’ with their ‘Model S’ car to the database tables. Executing INSERT statements that reference only one table at a time in the view are successful since they abide by the rules.

So far, it looks like we can use a view to modify data on a simple join. I am about to spoil your dream. We can not DELETE any records from the database. Both the single and double table statements fail.

Next time, we will be exploring INSTEAD OF TRIGGERS. Maybe this object can help us code the correct actions to be used on the view.

The second view that I am going to create has the same inner join between the [MAKES] and [MODELS] table. However, it is aggregating the data by using the GROUP BY clause and AVG function. Both of these constructs break the rules governing modifications to views.

The data in this view can be successfully queried using the SELECT statement below.

Since this view breaks the rules for data modifications, all data manipulation statements fail horribly.

To recap this article, typical uses of a view are the following:

  1. To simplify or customize the perception each user has of the database.
  2. Security mechanism to grant users access to the view, not the underlying base tables.
  3. To provide a backward compatible interface to emulate a table whose schema has changed.

Yet, many information technology professionals do not know that views can be used to modify data in the underlying tables. If you follow the rules defined by the database engine, most data manipulation statements can be performed. We saw that we had issues when deleting from a view.

Next time, we will be exploring INSTEAD OF TRIGGERS. Maybe this object can help us code the correct actions to be used on the view.

Related posts

Leave a Comment