Oracle PL/SQL – INSTEAD OF Trigger example

This article shows you how to use INSTEAD OF TRIGGER to update

  1. A noneditioning view
  2. A nested table column of a noneditioning view

This INSTEAD OF triggers provide a way to modify views that cannot be modified directly through DML statements like INSERT, UPDATE and DELETE

An INSTEAD OF trigger is

  1. Always a row-level trigger.
  2. Can read OLD and NEW values, but cannot change them.
  3. Cannot be conditional. Means we can not add WHEN or IF condition.

1. Insert Data into a View

In this example, we have created a INSTEAD OF trigger which will insert rows into respective tables of a view when we execute the insert statement on a view.

1.1 Create tables.

customer_details

CREATE TABLE customer_details
(
	customer_id number(10) primary key,
	customer_name varchar2(20),
	country varchar2(20)
);
projects_details

CREATE TABLE projects_details
(
	project_id number(10) primary key,
	project_name varchar2(30),
	project_start_Date date,
	customer_id number(10) references customer_details(customer_id)
);

1.2 Create customer_projects_view view to get results of customers and their projects.

customer_projects_view

CREATE OR REPLACE VIEW customer_projects_view AS
   SELECT cust.customer_id, cust.customer_name, cust.country,
          projectdtls.project_id, projectdtls.project_name, 
		  projectdtls.project_start_Date
   FROM customer_details cust, projects_details projectdtls
   WHERE cust.customer_id = projectdtls.customer_id;

1.3 If we INSERT INTO customer_projects_view view directly, the database will raise the following error :


INSERT INTO customer_projects_view VALUES (1,'XYZ Enterprise','Japan',101,'Library management',sysdate);

-- output
SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
01779. 00000 -  "cannot modify a column which maps to a non key-preserved table"
*Cause:    An attempt was made to insert or update columns of a join view which
           map to a non-key-preserved table.
*Action:   Modify the underlying base tables directly.

1.4 Instead, we should create a INSTEAD OF trigger on the view to perform the actual operation.

trg_cust_proj_view_insert

CREATE OR REPLACE TRIGGER trg_cust_proj_view_insert
   INSTEAD OF INSERT ON customer_projects_view
   DECLARE
     duplicate_info EXCEPTION;
     PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
   BEGIN
     
   INSERT INTO customer_details
       (customer_id,customer_name,country)
     VALUES (:new.customer_id, :new.customer_name, :new.country);
     
   INSERT INTO projects_details (project_id, project_name, project_start_Date, customer_id)
   VALUES (
     :new.project_id,
     :new.project_name,
     :new.project_start_Date,
     :new.customer_id);
  
   EXCEPTION
     WHEN duplicate_info THEN
       RAISE_APPLICATION_ERROR (
         num=> -20107,
         msg=> 'Duplicate customer or project id');
   END trg_cust_proj_view_insert;

1.5 Insert into view again. The INSTEAD OF trigger will be fired and insert the data into the actual table.


INSERT INTO customer_projects_view VALUES (1,'XYZ Enterprise','Japan',101,'Library management',sysdate);

INSERT INTO customer_projects_view VALUES (2,'ABC Infotech','India',202,'HR management',sysdate);

1.6 Select table.


SELECT * FROM customer_details;
CUSTOMER_ID CUSTOMER_NAME COUNTRY
1 XYZ Enterprise Japan
2 ABC Infotech India

SELECT * FROM projects_details;
PROJECT_ID PROJECT_NAME PROJECT_START_DATE CUSTOMER_ID
101 Library management 25-JUN-17 1
202 HR management 25-JUN-17 2

1.7 Select view.


SELECT * FROM customer_projects_view;
CUSTOMER_ID CUSTOMER_NAME COUNTRY PROJECT_ID PROJECT_NAME PROJECT_START_DATE
1 XYZ Enterprise Japan 101 Library management 25-JUN-17
2 ABC Infotech India 202 HR management 25-JUN-17

2. Nested table view column

Example to insert data into a nested table view column with INSTEAD OF Trigger.

2.1 Create table, type, nested table view column for testing.

vehicle_mfg_company_details

CREATE TABLE vehicle_mfg_company_details (
  company_id number(10) primary key,
  company_name varchar2(50) NOT NULL
);
vehicle_details

CREATE TABLE vehicle_details (
  vehicle_id  number(10) primary key,
  company_id  number(10) references vehicle_mfg_company_details(company_id),
  vehicle_model_name varchar2(50) NOT NULL
);
nestedTableEle

CREATE OR REPLACE TYPE nestedTableEle
IS
OBJECT (
  vehicle_id NUMBER(10),
  vehicle_model_name VARCHAR2(50)
  );
vehicle_details_list_

-- nested table view column
CREATE OR REPLACE TYPE vehicle_details_list_ IS
  TABLE OF nestedTableEle;

2.2 Create a View.

company_vehicles_view

CREATE OR REPLACE VIEW company_vehicles_view AS
  SELECT company.company_id, 
         company.company_name,
         CAST (MULTISET (SELECT vehicle.vehicle_id, vehicle.vehicle_model_name
                         FROM vehicle_details vehicle
                         WHERE vehicle.company_id = company.company_id
                        )
                        AS vehicle_details_list_
              ) vehiclelist
FROM vehicle_mfg_company_details company;

2.3 Insert data into nested table view column.


-- no error.
INSERT INTO vehicle_mfg_company_details VALUES (101,'Ford');

-- error
INSERT INTO TABLE (
  SELECT vw.vehiclelist 
  FROM company_vehicles_view vw
  WHERE company_id = 101
)
VALUES (1, 'EcoSport');

-- output
SQL Error: ORA-25015: cannot perform DML on this nested table view column
25015. 00000 -  "cannot perform DML on this nested table view column"
*Cause:    DML cannot be performed on a nested table view column except through
           an INSTEAD OF trigger
*Action:   Create an INSTEAD OF trigger over the nested table view column
           and then perform the DML.

2.4 Create a INSTEAD OF trigger

trg_comp_vehicles_view_insrt

CREATE OR REPLACE TRIGGER trg_comp_vehicles_view_insrt
  INSTEAD OF INSERT ON NESTED TABLE vehiclelist OF company_vehicles_view
  REFERENCING NEW AS Vehicle
              PARENT AS Company
  FOR EACH ROW
BEGIN
  -- Insert on nested table translates to insert on base table.
  INSERT INTO vehicle_details (
    vehicle_id,
    company_id,
    vehicle_model_name
  )
  VALUES (
    :Vehicle.vehicle_id,
    :Company.company_id,
    :Vehicle.vehicle_model_name
  );
END;

2.5 Insert data into nested table view column again.


INSERT INTO TABLE (
  SELECT vw.vehiclelist 
  FROM company_vehicles_view vw
  WHERE company_id = 101
)
VALUES (1, 'EcoSport');

INSERT INTO TABLE (
  SELECT vw.vehiclelist 
  FROM company_vehicles_view vw
  WHERE company_id = 101
)
VALUES (2, 'Endeavour');

2.6 Display table.


select * from vehicle_mfg_company_details;
COMPANY_ID COMPANY_NAME
101 Ford

select * from vehicle_details;
VEHICLE_ID COMPANY_ID VEHICLE_MODEL_NAME
1 101 EcoSport
2 101 Endeavour

References

  1. Instead OF Trigger:- Oracle official docs
  2. PL/SQL Triggers :- Oracle official docs
  3. Oracle PL/SQL – Before UPDATE Trigger example
author image

Dhaval Dadhaniya

Dhaval Dadhaniya is a software engineer by profession and reader/writter by passion. He is working in a well reputed MNC as an applications Developer with 5 years of experience. He have good understanding and knowledge of Java, Database, Spring, Hibernate and exploring other technologies related to Software development. Befriend him on Facebook Read all published posts by

Comments

avatar
newest oldest most voted
Pavan Solapure
Guest
Pavan Solapure

Well this is something new 🙂 Thanks for sharing it Dhaval

Dhaval Dadhaniya
Guest
Dhaval Dadhaniya

You are welcome Pavan…:)
thanks for reading this…..

Immanuel
Guest
Immanuel

Nice Topic…great useful

Manoj Kailasa
Guest
Manoj Kailasa

Explanation is good. Please do post something more on triggers.