Updating view using instead of triggerhttp://www.orafaq.com/forum/./mv/msg/188572/589855/#msg_589855
I have two different tables having similar structure but data is coming from different source.
finally i want to update the view so that the it should affect the base table.
please suggest me if anything is wrong.

create table emp1 as
select empno,ename,job,deptno,sal from emp where deptno=10;
create table emp2 as
select empno,ename,job,deptno,sal from emp where deptno=20;
create view emp_view as select * from emp1 union all select * from emp2;
create or replace trigger emp_update_inst_trg
instead of update on emp_view
referencing old as old new as new
for each row
begin
update emp1 set sal=:new.sal where deptno in (select d.deptno from dept d,emp1 e1 where d.deptno=e1.deptno) and empno=:old.empno;
update emp2 set sal=:new.sal where deptno in (select d.deptno from dept d,emp2 e2 where d.deptno=e2.deptno) and empno=:old.empno;
end;
update emp_view set sal=sal+1000;
select * from emp_view;

Regards,
Nathan]]>sss111ind2013-07-11T13:06:15-00:00Re: Updating view using instead of triggerhttp://www.orafaq.com/forum/./mv/msg/188572/589858/#msg_589858
BlackSwan2013-07-11T13:14:31-00:00Re: Updating view using instead of triggerhttp://www.orafaq.com/forum/./mv/msg/188572/589859/#msg_589859
This mean you may not be consistent in your update.

Regards
Michel
]]>Michel Cadot2013-07-11T13:16:01-00:00Re: Updating view using instead of triggerhttp://www.orafaq.com/forum/./mv/msg/188572/589861/#msg_589861
The structure I represent you exactly same but really some columns are missing in second table.So normalization is not issue I think.

Michel,

What is inconsistent here please clarify it.Is there any problem if I do like this.
Thanks all for responding.

Regards,
Nathan

]]>sss111ind2013-07-11T13:27:56-00:00Re: Updating view using instead of triggerhttp://www.orafaq.com/forum/./mv/msg/188572/589865/#msg_589865
If they are not the same ones then the database is different for the different statements.

Regards
Michel
]]>Michel Cadot2013-07-11T13:41:15-00:00Re: Updating view using instead of triggerhttp://www.orafaq.com/forum/./mv/msg/188572/590005/#msg_590005
The basic purpose is to get the information from both the tables at once.

The fetched data which are now "pending" status should be marked as "success" by updating both tables and should not be fetched again.
Due to structural difference of two tables, union all must be used while querying from both tables if view is not created.

What if someone else changes the tables content in the middle of the trigger execution?
What if between the 2 updates some rows move from the second to the first one? The trigger result is functionally wrong (some employees have not their new salary).
It is just an example, many other problems may appear.

Regards
Michel
]]>Michel Cadot2013-07-14T06:42:59-00:00Re: Updating view using instead of triggerhttp://www.orafaq.com/forum/./mv/msg/188572/590242/#msg_590242
What could be the correct solution for this problem.

Regards,
Nathan]]>sss111ind2013-07-16T09:24:45-00:00Re: Updating view using instead of triggerhttp://www.orafaq.com/forum/./mv/msg/188572/590244/#msg_590244
For instance, can an employee be in both tables?

CREATE OR REPLACE PROCEDURE p_update_sal
IS
CURSOR c1 IS SELECT * FROM emp1 WHERE JOB='MANAGER' FOR UPDATE OF sal;
CURSOR c2 IS SELECT * FROM emp2 where job='CLERK' FOR UPDATE OF sal;
BEGIN
FOR i IN c1 loop
update emp1 set sal=sal+1000 where current of c1;
END loop;
FOR i IN c2 loop
UPDATE emp2 SET sal=sal+1000 WHERE CURRENT OF c2;
end loop;
end;
begin
P_UPDATE_SAL;
end;

Regards,
Nathan]]>sss111ind2013-07-16T15:22:34-00:00Re: Updating view using instead of triggerhttp://www.orafaq.com/forum/./mv/msg/188572/590279/#msg_590279
BlackSwan2013-07-16T16:09:18-00:00Re: Updating view using instead of triggerhttp://www.orafaq.com/forum/./mv/msg/188572/590287/#msg_590287
Because in SAP the code is going to use where only single call is necessary.Two call is not possible at a time.
For that purpose I tried with view but it was also not worked. Any suggestion regarding this please.