Oracle PL/SQL - INSTEAD OF triggers

Introduction

INSTEAD OF triggers exist only on views.

Their main purpose is to perform data modifications of views that are not otherwise updatable.

The following view isn't updatable because of the ORDER BY clause:

create or replace view v_emp as 
select empNo, eName 
from emp 
order by eName

However, the user wants to have a way of changing ENAME here because there is no access to the real table.

This task can be accomplished easily by using an INSTEAD OF trigger:

create or replace trigger v_emp_iu 
INSTEAD OF UPDATE 
on v_emp 
declare 
     v_error_tx VARCHAR2(256); 
begin 
      if updating('EMPNO') 
      then 
           v_error_tx:='You cannot update the PK!'; 
            raise_application_error (-20999,v_error_tx); 
      else 
           update emp 
            set eName = :new.eName 
           where empNo = :old.empNo; 
      end if; 
end;

All INSTEAD OF triggers are fired for each row and you cannot narrow down the event by column.

Instead you can check to see what columns are updated in the body of the trigger by using the UPDATING ('column_name') clause.

Oracle PL/SQL - INSTEAD OF triggers

Introduction

Related Topic