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.

The in operator may be used if you know the exact value you want to return for at least one of the columns.

0 Answers  

how to delete duplicate rows from a join tables(I have three tables on that join) how do you know which join is need to be used? The select statement I have is: SELECT gc_skill_type.skill_type, gc_area_tec.area, gc_technology.technology, gc_technology.id_technology, gc_area_tec.id_area_tec FROM gc_skill_type, gc_area_tec, gc_technology WHERE gc_area_tec.id_skill_type (+) = gc_skill_type.id_skill_type AND gc_technology.id_area_tec (+) = gc_area_tec.id_area_tec order by gc_skill_type.skill_type asc, gc_area_tec.area asc, gc_technology.technology asc

1 Answers   IAS,

Is pl sql useful?

0 Answers  

where are cookies actually stored on the hard disk? : Sql dba

0 Answers  

What is application trigger?

0 Answers  

What is the difference between the sql*loader and import utilities? : aql loader

0 Answers  

what is mean by forward declaration and where we'll use it.

4 Answers   TCS,

what is the syntax for using sql_variant_property? : Transact sql

0 Answers  

What is the main difference between a UNION statement and a UNION ALL statement? 1. A UNION statement eliminates duplicate rows; a UNION ALL statement includes duplicate rows. 2. A UNION statement can be used to combine any number of queries; a UNION ALL statement can be used to combine a maximum of two queries. 3. A UNION statement can only combine queries that have parallel fields in the SELECT list; a UNION ALL statement can combine queries with differing SELECT list structures. 4. A UNION statement cannot be used with aggregate functions; a UNION ALL statement can be used with aggregate functions. 5. There is no difference between the two statements; they are interchangeable.

2 Answers   Saman Bank, Sonata,

Why do we need cursors in pl sql?

0 Answers  

How do I run sql?

0 Answers  

What is a REF CURSOR? Compare strong and week ref cursor types.

6 Answers