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 command line end user interface - mysql? : Sql dba

939

---

Can you have a foreign key without a primary key?

917

---

what is cursor and its type, what is ref cursor write a syntax to pass ref cursor into procedure out fucntion and call the procedure

1245

---

Explain the rollback statement?

976

---

What are the possible values that can be stored in a boolean data field?

963

---

What is difference between sql and excel?

1035

---

Does pl sql work in mysql?

938

---

Can a table contain multiple foreign key’s?

961

---

what is self-join? : Sql dba

1084

---

How many types of normalization are there?

913

---

Are views faster than queries?

945

---

How many functions are there in sql?

975

---

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

978

---

What is the use of non clustered index?

949

---

What is left join in postgresql?

1008

---