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.

What are Modifiable and Non Modifiable Views?

A view is naturally modifiable if you do not require INSTEAD OF triggers to insert, delete or update data as well as if it complies to the restrictions discussed herewith. If the view query comprises of any of the mentioned constructs, then it is not naturally modifiable and therefore you cannot perform inserts, updates, or deletes on the view:

  • Set operators
  • Aggregate functions
  • GROUP BY, CONNECT BY, or START WITH clauses
  • The DISTINCT operator
  • Joins (however, some join views are updatable)

In case a view consists of pseudo columns or expressions, then it is only possible to update it with an UPDATE statement and that also when it does not refer to any such pseudo columns or for that matter, expressions.

Syntax of Instead-Of Trigger

CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF operation
ON view_name
FOR EACH ROW
BEGIN
	---Your SQL Code—
END;
/

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.

Examples

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

CREATE TABLE trainer
  ( 
    full_name VARCHAR2(20)
  );

Table 2- Subject

CREATE TABLE 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 Trigger

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

CREATE OR REPLACE TRIGGER tr_Io_Insert
INSTEAD OF INSERT ON vw_rebellionrider
FOR EACH ROW
BEGIN
  INSERT INTO trainer (full_name) VALUES (:new.full_name);
  INSERT INTO subject (subject_name) VALUES (:new.subject_name);
END

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.