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.

Does a primary key have to be a number?

961

---

How is data stored in sql?

1053

---

How to write a query to show the details of a student from students table whose

993

---

What is lexical units in pl sql?

1141

---

What is java sql package?

1090

---

Write a sql query to get the third highest salary of an employee from employee_table?

1067

---

What are secondary keys?

968

---

How do I access sql anywhere database?

969

---

What is sql used for?

1117

---

Is there a 64 bit version of ssms?

956

---

Explain mutating table error.

1200

---

How do I clear the screen in sql plus?

1037

---

what are the differences among rownum, rank and dense_rank? : Sql dba

1036

---

What are the methods of filing?

933

---

What is the purpose of the primary key?

1058

---