Instead of Triggers in Oracle

Instead of Triggers (Views) in Oracle with Examples

In this article, I am going to discuss Instead of Triggers (Views) in Oracle with Examples. Please read our previous article where we discussed How to Populate a Default Value using Triggers with Examples.

Instead of Triggers (Views) in Oracle.

We can use Instead of Triggers on the views not on tables. We have a table called customers where there are two columns cust_id and name. This table has three rows. Below is how the table is presented.

Instead of Triggers (Views) in Oracle

We have a view called customer_view. This view reads from the table customers. This view is an updatable view. The view will contain the same data as customers. There are no triggers created on the table and view. If we add a new record in the view customer_view then the record will automatically be added to the customers table.

Instead of Triggers (Views) in Oracle with Examples

If we have trigger with instead of then if a new record is added to the view customer_view then the record will not be added to customers table. The purpose of the “instead of” is to populate the complex values. This will help in database performance. We can even add data to the table customers by adding manually using code.

Instead of Triggers (Views) in Oracle with Examples

Let us try to do an example and see how the “instead of” clause works. We already have employees table here. We have to delete the row in the table where employee_id=1 by executing the below DELETE SQL Statement.

delete from employees where employee_id=1;

Make sure to check the row does not present. Once the row is deleted, let us go ahead and create a view from the table employees by executing the below SQL Statement

create or replace view emp_all_v as select * from employees;

Instead of Triggers in Oracle with Examples

So, the view emp_all_v is created. Let us go ahead and check if the data is present in the view emp_all_v by executing the below SELECT SQL Statement

select * from emp_all_v;

Instead of Triggers in Oracle with Examples

You can see the data in the view is the same as the table for employees. As of now, there is no trigger assigned to the view emp_all_v. Before creating the trigger let us try to insert a new row into the view emp_all_v by executing the below SQL Statement.

Insert into emp_all_v (employee_id, first_name, last_name, email, salary, hire_date, job_id)
Values ( 1, 'abc','abc','dotnet@gmail.com', 9100, sysdate,'AD_PRES');

From the above statement, we are inserting the employee_id, first_name, and last_name, and other columns and each column will be inserted into a specific column in the view. Let us go ahead and insert the new record.

Instead of Triggers in Oracle

So, the record is inserted. Let us check the original table and see if the new employee details are inserted or not by executing the below SELECT SQL Statement

select * from employees where employee_id=1;

Instead of Triggers in Oracle

So, the new details are present on the employees table. We do not need this record now. Let us roll back this record.

Instead of Triggers in Oracle

So, rollback is also done. Let us go ahead and create a trigger with the clause “instead of” by executing the below SQL Statement. Here, instead of inserting the record into the table, we will be printing the word TEST.

Create or replace trigger TEST_INSTEAD
Instead of insert 
On emp_all_v
Begin
DBMS_OUTPUT.PUTLINE(‘TEST’);
End;

So, from the triggering statement, we can see that we have used instead of insert and we are printing the word “TEST”. Let us go ahead and compile the trigger.

Instead of Triggers in Oracle

So, the trigger is compiled. Let us go ahead and insert the same record again and see if the record is inserted or not by executing the following INSERT Statement.

Insert into emp_all_v (employee_id, first_name, last_name, email, salary, hire_date, job_id)
Values ( 1, 'abc','abc','dotnet@gmail.com', 9100, sysdate,'AD_PRES');

Instead of Triggers

We can see the row is inserted but let us check the DBMS output.

Instead of Triggers

From the DBMS output, you can see the word test is printed. Let us go ahead and check the table employees and see if the record is inserted or not by executing the below SQL Statement.

select * from employees where employee_id=1;

Instead of Triggers

We do not see any details with the employee_id as 1. Let us now roll back again and try using another method of the trigger.

Instead of Triggers with Examples

Now, we can insert the record manually. Let us go ahead and design the trigger again and see if we can insert the record manually. We will be directly inserting the record to the table employees instead of inserting the record into emp_all_v by executing the below SQL Statement.

Create or replace trigger TEST_INSTEAD
Instead of 
Insert
On emp_all_v
Begin
Insert into employees (employee_id, first_name, last_name, email, salary, hire_date, job_id)
Values ( :new.employee_id, :new.first_name, :new.last_name, :new.email, :new.salary, :new.hire_date, :new.job_id;
DBMS_OUTPUT.PUT_LINE(‘TRIGGER’);
End;

So, from the above trigger statement, we can see that instead of inserting the record into the view emp_all_v we are directly inserting it into the table employees. Let us go ahead and execute the trigger.

Instead of Triggers with Examples

So, the trigger is compiled successfully. Let us go ahead and test the trigger by the insert into emp_all_v by executing the below SQL Statement

Insert into emp_all_v (employee_id, first_name, last_name, email, salary, hire_date, job_id)
Values ( 1, 'abc','abc','dotnet@gmail.com', 9100, sysdate,'AD_PRES');

Instead of Triggers with Examples

We can see the output says 1 row inserted. Let us go ahead and check the employees table to check if the trigger is working fine or not by executing the below SQL Statement

select * from employees where employee_id=1;

Instead of Triggers with Examples

So, we can see the record is present in the table employees instead of the view emp_all_v. This is how we use the instead of triggers in oracle. We have created a view with the table and learned how the trigger works on views and tables.

In the next article, I am going to discuss How to Manage Trigger Execution Orders in Oracle using Follows Clause with Examples. Here, in this article, I try to explain Instead of Triggers (Views) in Oracle with Examples. I hope you enjoy this Instead of Triggers (Views) in the Oracle article.

Leave a Reply

Your email address will not be published. Required fields are marked *