Back to: Oracle Tutorials for Beginners and Professionals
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.
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.
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.
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;
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;
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.
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;
So, the new details are present on the employees table. We do not need this record now. Let us roll back this record.
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.
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');
We can see the row is inserted but let us check the DBMS output.
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;
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.
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.
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');
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;
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.