Oracle INSTEAD OF Triggers

Summary: in this tutorial, you will learn how to use an Oracle INSTEAD OF trigger to insert data into tables via a non-updatable view.

What is an instead of trigger in Oracle

An INSTEAD OF trigger is a trigger that allows you to update data in tables via their view which cannot be modified directly through DML statements.

When you issue a DML statement such as INSERT, UPDATE, or DELETE to a non-updatable view, Oracle will issue an error. Check it out for more information on the updatable view.

If the view has an INSTEAD OF trigger, it will automatically skip the DML statement and execute other DML statements instead.

Note that an INSTEAD OF trigger is fired for each row of the view that gets modified.

In Oracle, you can create an INSTEAD OF trigger for a view only. You cannot create an INSTEAD OF trigger for a table.

The following illustrates the syntax of creating an INSTEAD OF trigger:

CREATE [OR REPLACE] TRIGGER trigger_name INSTEAD OF {INSERT | UPDATE | DELETE} ON view_name FOR EACH ROW BEGIN EXCEPTION ... END;
Code language: SQL (Structured Query Language) (sql)

In this syntax:

  • First, specify the name of the trigger after the CREATE TRIGGER keywords. Use OR REPLACE if you want to modify an existing trigger.
  • Second, use the INSTEAD OF keywords followed by an operation such as INSERT, UPDATE, and DELETE.
  • Third, specify the name of the view with which the trigger is associated.
  • Finally, specify the code that executes instead of the INSERT, UPDATE, and DELETE.

Oracle INSTEAD OF trigger example

We will use the customers and contacts tables from the sample database for the demonstration.

PL/SQL SELECT INTO example

First, create a view based on the customers and contacts tables:

CREATE VIEW vw_customers AS SELECT name, address, website, credit_limit, first_name, last_name, email, phone FROM customers INNER JOIN contacts USING (customer_id);
Code language: SQL (Structured Query Language) (sql)

Next, attempt to insert a new customer and contact into the underlying tables via the view vw_customers:

INSERT INTO vw_customers( name, address, website, credit_limit, first_name, last_name, email, phone ) VALUES( 'Lam Research', 'Fremont, California, USA', 'https://www.lamresearch.com/', 2000, 'John', 'Smith', 'john.smith@lamresearch.com', '+1-510-572-0200' );
Code language: SQL (Structured Query Language) (sql)

Oracle issued the following error:

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.
Code language: SQL (Structured Query Language) (sql)

Then, create an INSTEAD OF trigger on the view vw_customers:

CREATE OR REPLACE TRIGGER new_customer_trg INSTEAD OF INSERT ON vw_customers FOR EACH ROW DECLARE l_customer_id NUMBER; BEGIN -- insert a new customer first INSERT INTO customers(name, address, website, credit_limit) VALUES(:NEW.NAME, :NEW.address, :NEW.website, :NEW.credit_limit) RETURNING customer_id INTO l_customer_id; -- insert the contact INSERT INTO contacts(first_name, last_name, email, phone, customer_id) VALUES(:NEW.first_name, :NEW.last_name, :NEW.email, :NEW.phone, l_customer_id); END;
Code language: SQL (Structured Query Language) (sql)

In this trigger, we inserted a new customer, get customer id, and use that id to insert a new contact.

After that, execute the following statement again:

INSERT INTO vw_customers( name, address, website, credit_limit, first_name, last_name, email, phone ) VALUES( 'Lam Research', 'Fremont, California, USA', 'https://www.lamresearch.com/', 2000, 'John', 'Smith', 'john.smith@lamresearch.com', '+1-510-572-0200' );
Code language: SQL (Structured Query Language) (sql)

This picture illustrates the INSTEAD OF trigger:

Oracle Instead Of Trigger

Finally, verify data from the customers table:

SELECT * FROM customers ORDER BY customer_id DESC FETCH FIRST ROWS ONLY;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

Oracle Instead Of Trigger example

As you can see, the new customer has been inserted successfully.

This statement returns the contact of the new customer:

Oracle Instead Of Trigger example table

In this tutorial, you have learned how to use an Oracle INSTEAD OF trigger to insert data into the base tables via a non-updatable view.

Was this tutorial helpful?