Instead of Insert

Most information technology professionals do not know that data can be modified via a VIEW. The INSERT, UPDATE, and DELETE statements of the 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.

I am going to continue my exploration of updatable views. Last time, we were using the [AUTOS] sample database and discovered that DELETE statements were not working the way we wanted to. Duplicate information such as [MAKER_ID2] had to be supplied in the view to allow DML statements to execute correctly. Last but not least, we could not perform an action on two tables at the same time.

Today, I will be looking at how INSTEAD OF TRIGGERS can be used to make the view work the way we want with DML statements.

Before we get into the nitty-gritty trigger details, we need to reload our test environment. The main point is that we wanted remove the old views and redefine a new view as below to removed duplicate information. The enclosed script has code to perform the following actions in the AUTOS database.

5A – Remove aggregate view
5B – Reload tables with good data
5C – Create a instead of trigger (insert)
5D – Test insert actions of instead of trigger
5E – Create a instead of trigger (delete)
5F – Test delete actions of instead of trigger
5G – Create a instead of trigger (update)
5H – Test update actions of instead of trigger


Now that we have the view the way we want, we can add the INSTEAD OF trigger. For each action, we are going to define a trigger on the [VW_JOIN_MAKES_2_MODELS] view. Today, we are going to concentrate on the INSERT action today.

Like most Data Definition Language (DDL) constructs, a user defined trigger has three operations associated with it: CREATE TRIGGER, ALTER TRIGGER and DROP TRIGGER.

Since the view is a join between two tables, we need to know if data is going to be inserted into the 1 = MAKES, 2 = MODELS or 3 = BOTH tables.

Triggers in general have inserted and deleted tables to determine the state of the data. The common table expression in the trigger checks the fields in the inserted table to determine the target table to work with.

Since the MAKER_ID and and MODEL_ID are auto increment fields, the end user updating the view has to supply the corresponding natural keys, MAKER_NM and and MODEL_NM. The trigger below accounts for the rare case in which the user does not supply any key value during a INSERT and a user defined error is raised when this situation happens.

Our first action should be a query of the view to see what information is stored in the tables.

Lets get the negative testing out of the way by trying to insert data into the view without a natural key.

The first positive test is to insert data into both MAKES or MODELS table separately.

The second positive test is to validate that inserting data into both the MAKES and MODELS tables at the same time works. This is a new functionality that is not available in a normal view.

Our last action should be a query of the view to see the final state of the information in the tables.

In summary, we were able to add a INSTEAD OF INSERT trigger to our user defined view to make it function correctly with a INSERT statement. While the next two articles focus on making our view updatable, these trigger can be used for other purposes.

Views can be used to partition data.

Vertical partitioning can be used to divide a table with a large number of columns into two or more tables. The same techniques used in these articles can be applied to a view that joins the tables in a logical one.

Horizontal partitioning is when data is split apart at some type of boundary. Typical boundaries are event dates or key values from a dimension table. This is a quick glance at a topic I will be covering in the future.

In a nutshell, the INSTEAD OF TRIGGER is a powerful construct that should be used when to creating an updatable view.

Related posts

Leave a Comment