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.