Answer Posted / 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.

what is the difference between a local and a global temporary table? : Sql dba

1117

---

What is java sql driver?

1174

---

What is spool?

1104

---

What is the usage of the distinct keyword?

1270

---

i have some prob lem to tell me about my self in interview first round ...

2213

---

How can a function retun more than one value in oracle with proper example?

1119

---

What is the difference between the conventional and direct path loads? : aql loader

1468

---

what is the difference between cluster and non cluster index? : Sql dba

1071

---

Define tables and fields in a database

1173

---

what is primary key? : Sql dba

1074

---

Which command is used to call a stored procedure?

1047

---

What is the difference between database trigger and stored procedure?

1128

---

What are the steps for performance tuning.

1391

---

table structure: ---------------- col1 col2 ----- ----- 01-mar-2012 11:12:46 01-mar-2012 11:12:10 01-mar-2012 11:12:46 01-mar-2012 11:11:23 Write a query to display the result as shown below: col1 col2 ----- ----- 01-mar-2012 11:12:46 01-mar-2012 11:12:10

4453

---

How to sort the rows in sql.

1113

---