INSTEAD OF trigger v16

Suggest edits

This example shows an INSTEAD OF trigger for inserting a new employee row into the emp_vw view. The CREATE VIEW statement creates the emp_vw view by joining the two tables. The trigger adds the corresponding new rows into the emp and dept tables, respectively, for a specific employee.

CREATE VIEW emp_vw AS SELECT * FROM emp e JOIN dept d USING(deptno);
CREATE VIEW

CREATE OR REPLACE TRIGGER empvw_instead_of_trig
    INSTEAD OF INSERT ON emp_vw
    FOR EACH ROW
DECLARE
    v_empno         emp.empno%TYPE;
    v_ename         emp.ename%TYPE;
    v_deptno        emp.deptno%TYPE;
    v_dname         dept.dname%TYPE;
    v_loc           dept.loc%TYPE;
    v_action        VARCHAR2(7);
BEGIN
    v_empno     := :NEW.empno;
    v_ename     := :New.ename;
    v_deptno    := :NEW.deptno;
    v_dname     := :NEW.dname;
    v_loc       := :NEW.loc;
     INSERT INTO emp(empno, ename, deptno) VALUES(v_empno, v_ename, v_deptno);
     INSERT INTO dept(deptno, dname, loc) VALUES(v_deptno, v_dname, v_loc);
END;
CREATE TRIGGER

Next, insert the values into the emp_vw view. The insert action inserts a new row and produces the following output:

INSERT INTO emp_vw (empno, ename, deptno, dname, loc ) VALUES(1234, 'ASHTON', 50, 'IT', 'NEW JERSEY');
Output
INSERT 0 1
SELECT empno, ename, deptno FROM emp  WHERE deptno = 50;
Output
 empno | ename  | deptno
-------+--------+--------
  1234 | ASHTON |     50
(1 row)
SELECT * FROM dept  WHERE deptno = 50;
Output
 deptno | dname |    loc
--------+-------+------------
     50 | IT    | NEW JERSEY
(1 row)

Similarly, if you specify an UPDATE or DELETE statement, the trigger performs the appropriate actions for UPDATE or DELETE events.

← Prev

After row-level trigger

↑ Up

Trigger examples

Next →

Compound triggers