INSTEAD OF TRIGGER EXAMP
Instead of trigger is used to provide a way of modifying views that cannot be modified directly through SQL DML statements because the view is not inherently modifiable.
ex :- composite views . , views that are based on two tables with aggregation functions and etc.
You can write INSERT, UPDATE, and DELETE statements against the view.
The INSTEAD OF trigger works invisibly in the background performing the action coded in the trigger body directly on the underlying tables.
example :- the following is a complex view and cannot be modified .
create view complex_view as
select e.deptno,d.dname,sum(e.Sal) sum_sal
from emp e join dept d on (e.deptno=d.deptno)
group by e.deptno,d.dname
SQL> select * from complex_view;
DEPTNO DNAME SUM_SAL
---------- -------------- ----------
10 software 9804
20 RESEARCH 10985
30 SALES 9401
Try to modify the conent of the view by using an update statement, it fails.
sQL> update complex_view set dname='sale' where dname ='SALES';
update complex_view set dname='IT' where dname ='SALES'
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
An instead of trigger can be used to modify the view, however the instead of trigger too cannot directly moidfy the view, so a workaround is done in the code and the base tables are modified to later reflect in the complex view.
create or replace trigger instead_of_trigger
instead of update on complex_view for each row
begin
update dept set dname = :new.dname where dname= :old.dname;
end;
SELECT * FROM DEPT; -- FOR VERIFICATION
update complex_view set dname='IT' where dname ='SALES';
SELECT * FROM COMPLEX_VIEW;-- FOR VERIFICATION
Comments
very helpful