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.

how to get a list of all tables in a database? : Sql dba

0 Answers  

What is a mutating table

5 Answers   TCS, Tech Mahindra,

How to make a copy values from one column to another in sql?

0 Answers  

What are schema-level triggers?

0 Answers  

What is inner join in sql?

0 Answers  

What are primary key and foreign key and how they work?

0 Answers  

what are the advantages of mysql in comparison to oracle? : Sql dba

0 Answers  

What is database sql?

0 Answers  

what are the differences between require and include, include_once and require_once? : Sql dba

0 Answers  

What is the best free sql database?

0 Answers  

What is primary key in db?

0 Answers  

1.when will you use week refcursor and when will you use strong ref cursor ? 2.what is the use of sql trace..how do you use it ? 3.can you please send all the sql plus commands...like set line 6000....

1 Answers   Satyam,