Answer / sohail

Instead of trigger is used to update the database tables
associated with the view instead of updating the view
directly.

Answer / aadi

Using an INSTEAD OF Trigger to Update on a View

After you create one or more tables (like those named dept
and emp in the following example), and then created a view
(like the one named manager_info) from dept and emp, you can
use an INSTEAD OF trigger to update that view.

The following CREATE TRIGGER statement creates
manager_info_update, an INSTEAD OF trigger that is designed
to update rows within the dept and emp tables through the
manager_info view.

CREATE TRIGGER manager_info_update
INSTEAD OF UPDATE ON manager_info
REFERENCING NEW AS n
FOR EACH ROW
(EXECUTE PROCEDURE updtab (n.empno, n.empname,
n.deptno,));

CREATE PROCEDURE updtab (eno INT, ename CHAR(20), dno INT,)
DEFINE deptcode INT;
UPDATE dept SET manager_num = eno where deptno = dno;
SELECT deptno INTO deptcode FROM emp WHERE empno = eno;
IF dno !=deptcode THEN
UPDATE emp SET deptno = dno WHERE empno = eno;
END IF;
END PROCEDURE;

After the tables, view, trigger, and SPL routine have been
created, the database server treats the following UPDATE
statement as a triggering event:

UPDATE manager_info
SET empno = 3666, empname = "Steve"
WHERE deptno = 01;

This triggering UPDATE statement is not executed, but this
event causes the trigger action to be executed instead,
invoking the updtab() SPL routine. The UPDATE statements in
the SPL routine update values into both the emp and dept
base tables of the manager_info view.

Answer / rao

If a view is created by complex join/set operators/distinct/
group by is called complex view.insert data into those table
then insetead of trigger not ordernary triggers

Answer / arvind pandita

In simple words Instead of Trigger is used to update base
tables through view based on base table.
This prevets us to update multiple tables in case of an
Complex view.

Answer / anil siddi

We cannot update the view directly if the viw define with
more than one table(objects).By using Instead of triggers we
can do that

Answer / bikram samal

it is use for modify any view which is based on base table where any DML statement can't modify.

Answer / bikram samal

we can use 'instead of trigger' for views. using these 'instead of trigger' we can perform DML operation on a complex view.

Answer / ashwin

Hi all,
If a view is been created by using two base tables then
no manipulations will be done on the view in general when
you perform any dml operation on it.
But, when you apply instead of trigger on view created
from two base tables then you can perform any dml operation
on that view.
Example:- Following example illustrates how to achieve it
a view empdept is created using emp & dept tables in user scott

create or replace view empdeptview as
select e.ename ,e.empno,e.sal,e.deptno,d.loc
from emp e,dept d
where
e.deptno=d.deptno;

Then after creating view create trigger insteadtrg1 on view
empdeptview in following manner.

create or replace trigger insteadtrg1 instead of update on
empdeptview
referencing new as new
for each row
begin
update emp set
ename=:new.ename,
empno=:new.empno,
sal=:new.sal,
deptno=(select deptno from dept where loc=:new.loc)
where empno=:old.empno;
if(sql%rowcount=0)then
raise_application_error(-20001,'error updating view');
end if;
end;
-------------------------------------------------------

Now after trigger creation perform update operation on view
empdept in following manner.

update empdept set ename='laxman' where ename='ram';

result will be 1 row updated.
this updation would not have been possible if u dont use
instead of trigger.

Answer / suresh somayajula

"Instead Of Triggers " are used to modify the view
which can't be modified by DML Statements.

Answer / amol garve

INSTEAD OF triggers describe how to perform insert, update,
and delete operations on a views. INSTEAD OF triggers allow
user to treat view as a table. Using instead of trigger we
insert record in view which actully goes in table.

What cursor type do you use to retrieve multiple recordsets?

17 Answers   Apollo, CTS, IonIdea, Microsoft, Oracle, TCS, Vmoksha, Wipro,

What are nested triggers ?

6 Answers   Amazon, Appeal Soft, IBM, Infosys, TCS,

What will happen after commit statement ?

2 Answers  

What is the difference between row level and statement level trigger?

0 Answers  

What is the difference between the implicit and explicit cursors?

0 Answers  

What is record type in pl sql?

0 Answers  

Explain the rollback statement?

0 Answers  

What is sqlite format?

0 Answers  

What do you understand by exception handling in pl/sql?

0 Answers  

What are the rules to be applied to nulls whilst doing comparisons?

0 Answers  

What are the different sql languages?

0 Answers  

What is the purpose of the partition table?

0 Answers