Instead of Triggers on Views Example in Oracle Database

INSTEAD OF trigger example

Triggers are not very commonly used in Oracle apps customization. This is because Oracle discourages using triggers on base tables. For certain business triggers are created on custom tables but mainly for INSERT/UPDATE/DELETE. We shall discuss the INSTEAD OF trigger which is used very sparingly.
The INSTEAD OF trigger is always created on a VIEW instead of a table. The main reason for the existence of the INSTEAD OF trigger is for Oracle forms to insert data directly into the base table instead of inserting data into the view which in turn would insert data into the base table on which the VIEW is created. But why should we go into this trouble?
This because there could be a possibility of creating a view on a table but the view might not include the primary or the unique key of the table. In such a case the insert into the view will fail. To avoid this failure an INSTEAD OF trigger is created on this view so that the insert can be written separately in the trigger and successfully insert data.
The following is a demo of an INSTEAD OF trigger
Create a table

1 2 3 4 5 6 7 8 9 10

CREATE TABLE XX_DATA (   XXNUM      VARCHAR2(10 BYTE),   XXTITLE    VARCHAR2(20 BYTE),   BUYERID    VARCHAR2(20 BYTE),   BUYERNAME  VARCHAR2(20 BYTE),   ITEMNUM    VARCHAR2(10 BYTE),   ITEMQTY    VARCHAR2(10 BYTE) ) /

 Create a view on this table

1 2 3 4 5

CREATE OR REPLACE VIEW XX_DATA_V (xxnum, xxtitle, buyerid, buyername, itemnum, itemqty) AS    SELECT "XXNUM", "XXTITLE", "BUYERID", "BUYERNAME", "ITEMNUM", "ITEMQTY"      FROM xx_data /

Check the table

1	SELECT * from XX_DATA_V

No records in table

No records exist in the table now as no data has been inserted into it.
Insert data into the table and the view and query the view/table

1 2 3 4 5

INSERT INTO XX_DATA (XXNUM, XXTITLE, BUYERID) VALUES (10, 20, 30);    INSERT INTO XX_DATA_V (XXNUM, BUYERNAME, BUYERID) VALUES (20, 'TEST', 1.3);    SELECT * from XX_DATA_V;

Data inserted

Both records are inserted into the table.
Now let us create an INSTEAD OF trigger on the view, XX_DATA_V
This trigger will simply enter 2 hard coded columns into 1 row of the table for each insert call

1 2 3 4 5 6

CREATE OR REPLACE TRIGGER APPS.xx_data_ins_v INSTEAD OF INSERT ON APPS.XX_DATA_V FOR EACH ROW BEGIN     INSERT INTO xx_data (xxnum, xxtitle) VALUES (150, 'TT'); END;

Creating the INSTEAD OF trigger

Now let us insert a record into the view, XX_DATA_V

1	INSERT INTO XX_DATA_V (BUYERNAME, BUYERID) VALUES ('XXTEST', 9.5)

Let us query the view/table

1	SELECT * from XX_DATA_V

Table data

Notice the 3rd row at the bottom. This row has been inserted in the previous insert into the view. Since the view had an INSTEAD OF trigger on it the INSTEAD OF trigger executed the insert statement within it and hence none of the data, that was originally inserted, has gone into the table. The INSTEAD OF trigger has completely disregarded the statement.


OR Below is Simple Example

Instead of Triggers on Views Example in Oracle

Instead of triggers in oracle database are defined on views only. A normal DML trigger executes only when a DML operation is issued on a table. Whereas instead of trigger fires when a DML statment is issued on the view. Instead-of triggers must be row level.

Create Instead of Trigger - Example:

Take a look at the following view defintion:

Create or replace view emp_dept_join as
  Select d.department_id,
  d.department_name,
  e.first_name,
  e.last_name
  from   employees e,
         departments d
  where  e.department_id = d.department_id;

As the view consists of two table joins, it is illegal to insert records into this view as the insert requires both the underlying tables to be modified.

By creating an instead-of trigger on the view, you can insert the records into both the underlying tables. An examples is shown below:

CREATE OR REPLACE TRIGGER insert_emp_dept
  INSTEAD OF INSERT ON emp_dept_join
DECLARE
  v_department_id departments.department_id%TYPE;
BEGIN
  BEGIN
    SELECT department_id INTO v_department_id
    FROM   departments
    WHERE  department_id = :new.department_id;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      INSERT INTO departments (department_id, department_name)
             VALUES (dept_sequence.nextval, :new.department_name)
             RETURNING ID INTO v_department_id;
  END;

  INSERT INTO employees (employee_id, first_name, last_name, department_id)
         VALUES(emp_sequence.nextval, :new.first_name, :new.last_name, v_department_id);
END insert_emp_dept;
/