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.
| Is This Answer Correct ? | 7 Yes | 0 No |
Post New Answer View All Answers
What is normalization in a database?
What is a string data type in sql?
Explain the the delete statements in sql?
How to run sql commands in sql*plus?
What is row_number () in sql?
Can dml statements be used in pl/sql?
How to call a javascript function from pl sql?
What is a crud api?
Explain the rollback statement?
explain the delete statements in sql
what is sql in mysql? : Sql dba
write an sql query to get third maximum salary of an employee from a table named employee_table. : Sql dba
Which is faster truncate or drop?
How do I write a sql query in pgadmin 4?
What are pl sql procedures?
