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.

Here’s 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: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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, provide the name of the view with which the trigger is associated.
• Finally, write the code that executes when the event such as INSERT, UPDATE, and DELETE occurs on the view.

Oracle INSTEAD OF trigger example #

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

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: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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',
    '[email protected]',
    '+1-510-572-0200'
);
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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',
        '[email protected]',
        '+1-510-572-0200'
    );
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

This picture illustrates the INSTEAD OF trigger:

Finally, verify data from the customers table:

SELECT * FROM customers 
ORDER BY customer_id DESC
FETCH FIRST ROWS ONLY;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here is the output:

The output indicates that the trigger inserted the new customer into the customers table successfully.

This statement returns the contact of the new customer:

Summary #

• Use an Oracle INSTEAD OF trigger to insert data into the base tables via a non-updatable view.