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
CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF operation
FOR EACH ROW
---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
CREATE TABLE trainer
Table 2- Subject
CREATE TABLE subject
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.
CREATE OR REPLACE TRIGGER tr_Io_Insert
INSTEAD OF INSERT ON vw_rebellionrider
FOR EACH ROW
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