Instead-oF Triggers In Database

By Manish Sharma

Instead-oF Insert Trigger

So far we have learnt DML, DDL and System Event Triggers in Oracle Database. Today in this tutorial we will explore the concepts of Instead-of trigger in Oracle Database. This blog has been written keeping in mind the certification exam as well as the job interview. Hope you will enjoy reading it. If so then click here to tweet and share.

Instead Of Trigger

Instead-of triggers in oracle database provide a way of modifying views that cannot be modified directly through the DML statements. By using Instead-of triggers, you can perform Insert, Update, Delete and Merge operations on a view in oracle database.

Restriction on Instead-of View.

Instead-of triggers can control Insert, Delete, Update and Merge operations of the View, not the table. Yes you heard it right, you can write an instead-of trigger on Views only and not on tables in Oracle database. That is the restriction that you have to comply with. Along with this you even have to comply with every general restriction that is imposed on all types of triggers. We have discussed those in the Introduction of triggers section.

Uses of Instead-of trigger.

Since an Instead-of trigger can only be used with views therefore we can use them to make a non-updatable view updatable as well as to override the default behavior of views that are updatable.

Read Here: What are Modifiable and Non Modifiable Views

Syntax of instead-of trigger

 INSTEAD OF operation
 ON view_name
   ---Your SQL Code—
For line-by-line detailed explanation of the above syntax please refer my video tutorial. There I have explained all the clause and keyword of the syntax in detail.

So you asked…

When does an Instead-of trigger fires – Before or After the triggering event?

If you noticed carefully then you’ll see that we do not have either BEFORE or AFTER clause in the syntax. This is because unlike other triggers, instead-of trigger executes neither BEFORE nor AFTER but instead of a triggering event. That is why we do not need to specify either BEFORE or AFTER clause.


Instead-of Insert Trigger

Instead-of trigger can be best demonstrated using a View joining two or more tables. Thus in this example I will create two simple tables and will then create a view over them. After that I will create an Instead of trigger for Insert operation on this view.

Step1: Create Tables

Table 1- trainer
   full_name VARCHAR2(20)
Table 2- Subject
   subject_name VARCHAR2(15)

Insert dummy data into the above tables

 INSERT INTO trainer VALUES ('Manish Sharma');

 INSERT INTO subject VALUES ('Oracle');

Step 2: Create View

In this step I will create a view which will show you the combined result of the data from the two tables above.

Suggested Reading: How To Create SQL View in Oracle Database

 CREATE VIEW vw_rebellionrider AS
 SELECT full_name, subject_name FROM trainer, subject;
This is a non-updatable view which you can confirm by executing any DML statement over it. Error as a result of DML operation on this view will be your confirmation.

Step 3: Create Instead-of Insert Trigger

Next I will create an Instead-of Insert trigger over the view vw_rebellionrider that we created in step 2.

 INSTEAD OF INSERT ON vw_rebellionrider
  INSERT INTO trainer (full_name) VALUES (:new.full_name);
  INSERT INTO subject (subject_name) VALUES (:new.subject_name);

On successful execution, this trigger will insert a new row of data into both the underlying tables of the view vw_rebellionrider. You can confirm that by executing an insert DML over the view.

For better understanding I suggest you to watch the Video tutorial uploaded over my YouTube channel. Stay tuned because in the next tutorial we will learn how to create an Instead-of Update trigger in oracle database.

Help others in learning by sharing this tutorial on your social networks with your friends. Thanks & have a great day.

SQL Script and Presentation used

You can DOWNLOAD SQL script and presentation used in the Video and in this article.

NO SQL Script used in Video and in this article

Do Not Drink & Drive Use My Uber Code "UberRebellionRider"
SignUp using my referral code and get first ride worth $20 FREE

  • Manish Sharma Oracle Rebellion Rider
  • Manish Sharma Oracle certified SQL expert
  • Manish Sharma oracle certified associate
  • Manish Sharma oracle certified professional
  • View Manish Sharma's profile on LinkedIn
  •          View Manish Sharma's profile on LinkedIn