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.

Whate is use of MOD function in pl/sql.

3 Answers   Oracle,

Describe sql comments?

0 Answers  

what is transaction? : Sql dba

0 Answers  

How you will create Toad Function?

1 Answers  

How will you make performance better for application including front end and back

2 Answers   Metric Stream,

What is materialized view in sql?

0 Answers  

Suppose I have to create a view on a table, that is not yet been created by DBA. I khow the table structure. Is it possible to create the view before DBA creates this table? If yes then how? Is it possible to create synonym in this way?

4 Answers   IBM, Metric Stream,

What is a string data type in sql?

0 Answers  

What is union, minus and interact commands?

0 Answers  

What is trigger explain it?

0 Answers  

why use cursors?

5 Answers   Oracle,

how to create a new table in mysql? : Sql dba

0 Answers