INSTEAD OF trigger

Oracle PL/SQL INSTEAD OF trigger

oracle_pl/sql_instead_of_trigger

INSTEAD OF trigger provides a transparent way of modifying views that cannot be modified directly through DML statements(INSERT, UPDATE, and DELETE). These triggers are called INSTEAD OF triggers because, unlike other types of triggers, Oracle fires the trigger instead of executing the triggering statement.

You can write normal INSERT, UPDATE and DELETE statements against the view and the INSTEAD OF trigger is fired to update the underlying tables appropriately. INSTEAD OF triggers are activated for each row of the view that gets modified.

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 statement is issued on the view. You cannot create an INSTEAD OF trigger for a table. INSTEAD OF triggers must be row level.

Syntax:

CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF {INSERT | UPDATE | DELETE}
ON view_name
FOR EACH ROW
BEGIN
    EXCEPTION
    ...
END;

Example :

Create a view based on the emp and dept tables:

CREATE OR REPLACE VIEW vw_emp
AS
SELECT E.ENAME, E.JOB, E.HIREDATE,D.DNAME, D.LOC
  FROM EMP E
 INNER JOIN DEPT D
 USING (DEPTNO);
 

Attempt to insert a new emp and dept into the underlying tables via the view vw_emp :

INSERT INTO VW_EMP
    (ENAME, JOB, HIREDATE, DNAME, LOC)
VALUES
    ('emp_test', 'Tester', SYSDATE,'Testing', 'IND');

Oracle issued the following error:

ORA-01776: cannot insert NULL into ("dept"."deptno")

Now , create an INSTEAD OF trigger on the view vw_emp:

CREATE OR REPLACE TRIGGER new_emp_trg
INSTEAD OF INSERT ON vw_emp
FOR EACH ROW
    DECLARE
    l_empno NUMBER;
    l_deptno NUMBER;
    BEGIN
        SELECT MAX(empno)+10 INTO l_empno FROM emp;
        SELECT MAX(deptno)+10 INTO l_deptno FROM dept;
        
        INSERT INTO dept(deptno,dname,loc)
        VALUES(l_deptno,:new.dname,:new.loc);
        
        INSERT INTO emp(empno,ename,job,hiredate,deptno)
        VALUES(l_empno,:new.ename,:new.job,:new.hiredate,l_deptno);

    END;

Now , execute the following statement again:

INSERT INTO VW_EMP
    (ENAME, JOB, HIREDATE, DNAME, LOC)
VALUES
    ('emp_test', 'Tester', SYSDATE,'Testing', 'IND');
    

Finally, verify data from the emp table:

SELECT * FROM EMP ORDER BY EMPNO DESC FETCH FIRST 2 ROWS ONLY;

    EMPNO    ENAME     JOB     MGR     HIREDATE                SAL     COMM    DEPTNO
1    7944    emp_test  Tester          10-11-2019 05:48:15 PM                  50
2    7934    MILLER    CLERK   7782    23-01-1982              1300.00         10


This article is contributed by Aditya. If you like dEexams.com and would like to contribute, you can also write your article here or mail your article to admin@deexams.com . See your article appearing on the dEexams.com main page and help others to learn.


Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.


Python if , elif and else

Python Conditions and If statements

  • 0
Python for beginners

Learning Python Part 1

  • 3
Struct Alignment and Padding

Struct Alignment and Padding in C++ And C

  • 0
Friend function

Friend function C++

  • 0
Pointers

C++ Pointers

  • 0
Structures

C++ Structures

  • 0
Types of Inheritance in C++

Inheritance and access specifiers C++

  • 0
Java date pattern

Java Date Pattern Syntax

  • 0
Java Date and Calendar

Java Date formats

  • 0
JAVA Data Type

Data types in Java

  • 0
Java unreachable code

Unreachable Code Error in Java

  • 0

Post Comment

Comments(0)

WEB TECHNOLOGY

Articles

×

Forgot Password

Please enter your email address below and we will send you information to change your password.