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.

How many sql core licenses do I need?

882

---

Why is sql important?

1026

---

What is a system versioned table?

928

---

what are all the different normalizations? : Sql dba

960

---

Which sql most popular?

935

---

What are the two types of exceptions.

1106

---

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

969

---

What is the difference between Union and Union all. Which is faster.

1341

---

what is row? : Sql dba

1166

---

Which certification is best for sql?

938

---

Is sql better than excel?

1060

---

explain advantages of myisam over innodb? : Sql dba

1015

---

Which are the different case manipulation functions in sql?

953

---

What is cursor in pl sql with examples?

953

---

What are sql built in functions?

894

---