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.

Explain the select statement in sql?

1115

---

What is data manipulation language?

1193

---

How do I view an execution plan in sql?

989

---

what are the different functions in sorting an array? : Sql dba

1027

---

How to create your own reports in sql developer?

1025

---

Can you have more than one trigger on a table?

1003

---

Can we use rowid as primary key?

990

---

What is raid? How does it help storage of databases?

1087

---

how many sql ddl commands are supported by 'mysql'? : Sql dba

1026

---

What are the different ddl commands in sql?

1173

---

How to disable a trigger name update_salary?

1267

---

What are sql injection vulnerabilities?

989

---

What is the sql*loader? : aql loader

1057

---

What do we need to check in database testing?

1023

---

What are different functions in sql?

951

---