INSTEAD OF DML Trigger Example

This Article will help you to understand “INSTEAD OF DML trigger statement” with examples and it’s detailed description.

An INSTEAD OF trigger is A DML trigger created on either a noneditioning view or a nested table column of a noneditioning view.

The database will fire the INSTEAD OF trigger instead of running the triggering DML statement. An INSTEAD OF trigger cannot be conditional.

Means we can not add WHEN or IF condition on INSTEAD OF Trigger. We can update a view which is not inherently updatable by INSTEAD OF TRIGGER.

We can write the INSTEAD OF TRIGGER to define what operation was intended in DML event and what operation we want to perform via this trigger on underlying tables.

An INSTEAD OF trigger is always a row-level trigger. An INSTEAD OF trigger can read OLD and NEW values, but cannot change them.

Examples

Insert data into inherently updatable view.

(1) Insert data into inherently updatable view using INSTEAD OF Trigger.

Description

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

First, we will create tables customer_details (as a master table) and projects_details (as a details table).

One customer can have many projects so customer_id will be foreign key in projects_details table which references to customer_id of customer_details table.

Then we will create VIEW “customer_projects_view” to get results of customers and their projects.

We will try to INSERT INTO customer_projects_view and observe the error.

So we will create INSTEAD OF trigger “trg_cust_proj_view_insert” to process the INSERT operation on the view. This trigger will insert data into base tables.

Then we will again try to INSERT INTO customer_projects_view and observe the results.

Code
Output
table CUSTOMER_DETAILS created.
Output
table PROJECTS_DETAILS created.
Output
view CUSTOMER_PROJECTS_VIEW created.

It will throw error and will not allow to insert into view.

Output
Error report: 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.
Output
TRIGGER TRG_CUST_PROJ_VIEW_INSERT compiled
Output
1 rows inserted. 1 rows inserted.
CUSTOMER_ID CUSTOMER_NAME COUNTRY
1 XYZ Enterprise Japan
2 ABC Infotech India
PROJECT_ID PROJECT_NAME PROJECT_START_DATE CUSTOMER_ID
101 Library management 25-JUN-17 1
202 HR management 25-JUN-17 2
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
Insert into nested table column of a view.

(2) Insert data into nested table column of a view with INSTEAD OF Trigger.

Description

In this example we have created an INSTEAD OF trigger which will insert rows into base table of a view when we execute insert into statement on view with nested table column.

First, we will create tables vehicle_mfg_company_details (as a master table) and vehicle_details (as a details table).

One company can have many vehicles so company_id will be foreign key in vehicle_details table which references to company_id of vehicle_mfg_company_details table.

We will create an Object type of nested table element as nestedTableEle.

Then we will create type of nested table as vehicle_details_list_ of nestedTableEle.

Then we will create VIEW company_vehicles_view with nested column to get results of vehicle mfg company and their vehicle details.

Create INSTEAD OF trigger trg_comp_vehicles_view_insrt to process the INSERT operation on the view. This trigger will insert data into base table.

Then we will modify vehiclelist column of company_vehicles_view and observe the results.

Code
Output
table VEHICLE_MFG_COMPANY_DETAILS created.
Output
table VEHICLE_DETAILS created.
Output
TYPE NESTEDTABLEELE compiled
Output
TYPE VEHICLE_DETAILS_LIST_ compiled
Output
view COMPANY_VEHICLES_VIEW created.

Try to modify vehiclelist column of company_vehicles_view. We will receive an error “cannot perform DML on this nested table view column”. So in next step we will create an INSTEAD of Trigger to resolve this issue.

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.
Output
TRIGGER TRG_COMP_VEHICLES_VIEW_INSRT compiled
Output
1 rows inserted. 1 rows inserted.
COMPANY_ID COMPANY_NAME
1 Ford
VEHICLE_ID COMPANY_ID VEHICLE_MODEL_NAME
1 101 EcoSport
2 101 Endeavour
References
  1. Oracle View:- Oracle official docs
  2. Instead OF Trigger:- Oracle official docs
  3. PL/SQL Triggers :- Oracle official docs
It's good to share...Share on FacebookTweet about this on TwitterShare on LinkedInPin on PinterestShare on Google+Email this to someone

Leave a Reply

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