Home » SQL & PL/SQL » SQL & PL/SQL » Updating view using instead of trigger (Oracle 10g)
Updating view using instead of trigger [message #589855] Thu, 11 July 2013 08:06 Go to next message
sss111ind
Messages: 508
Registered: April 2012
Location: India
Senior Member

Hi all,

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
Re: Updating view using instead of trigger [message #589858 is a reply to message #589855] Thu, 11 July 2013 08:14 Go to previous messageGo to next message
BlackSwan
Messages: 23191
Registered: January 2009
Senior Member
duplicate data in more than 1 table violates Third Normal Form
Re: Updating view using instead of trigger [message #589859 is a reply to message #589855] Thu, 11 July 2013 08:16 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We will wait for Kevin who is our expert in "instead of" trigger but I'm not sure the 2 update statements have the same SCN reference which may also not be the same one than the original update statement.
This mean you may not be consistent in your update.

Regards
Michel
Re: Updating view using instead of trigger [message #589861 is a reply to message #589859] Thu, 11 July 2013 08:27 Go to previous messageGo to next message
sss111ind
Messages: 508
Registered: April 2012
Location: India
Senior Member

BlackSwan,

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

Re: Updating view using instead of trigger [message #589865 is a reply to message #589861] Thu, 11 July 2013 08:41 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What might be inconsistent is the SCN of each statement.
If they are not the same ones then the database is different for the different statements.

Regards
Michel
Re: Updating view using instead of trigger [message #590005 is a reply to message #589865] Sat, 13 July 2013 08:20 Go to previous messageGo to next message
sss111ind
Messages: 508
Registered: April 2012
Location: India
Senior Member

Hi All,

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.

Please suggest me on the same if above process is not correct.

Regards,
Nathan
Re: Updating view using instead of trigger [message #590038 is a reply to message #590005] Sat, 13 July 2013 13:06 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said, it is more likely not correct.

Regards
Michel
Re: Updating view using instead of trigger [message #590062 is a reply to message #589865] Sat, 13 July 2013 22:43 Go to previous messageGo to next message
manubatham20
Messages: 495
Registered: September 2010
Location: Champaign, IL
Senior Member

Michel Cadot wrote on Thu, 11 July 2013 13:41
What might be inconsistent is the SCN of each statement.
If they are not the same ones then the database is different for the different statements.


Didn't get it? Is that mean that SCN will be different for 2 different update statements. Even if, how it is stopping to achieve the OPs purpose?

Well below is what TOM says:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5226026422731#2090908600346797476

Thanks,
Manu

[Updated on: Sat, 13 July 2013 22:48]

Report message to a moderator

Re: Updating view using instead of trigger [message #590068 is a reply to message #590062] Sun, 14 July 2013 01:42 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Well below is what TOM says


Yes, this is also my point.

Quote:
Even if, how it is stopping to achieve the OPs purpose?


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
Re: Updating view using instead of trigger [message #590242 is a reply to message #590068] Tue, 16 July 2013 04:24 Go to previous messageGo to next message
sss111ind
Messages: 508
Registered: April 2012
Location: India
Senior Member

Hi Michel,

What could be the correct solution for this problem.

Regards,
Nathan
Re: Updating view using instead of trigger [message #590244 is a reply to message #590242] Tue, 16 July 2013 04:37 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends on the actual data you can have in both tables.
For instance, can an employee be in both tables?

Regards
Michel
Re: Updating view using instead of trigger [message #590277 is a reply to message #590244] Tue, 16 July 2013 10:22 Go to previous messageGo to next message
sss111ind
Messages: 508
Registered: April 2012
Location: India
Senior Member

Suppose two tables must be needed then can I do like this with a procedure or still have some issue.Please suggest.

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
Re: Updating view using instead of trigger [message #590279 is a reply to message #590277] Tue, 16 July 2013 11:09 Go to previous messageGo to next message
BlackSwan
Messages: 23191
Registered: January 2009
Senior Member
why is PL/SQL used when can be done as 2 plain SQL statements?
Re: Updating view using instead of trigger [message #590287 is a reply to message #590279] Tue, 16 July 2013 12:54 Go to previous message
sss111ind
Messages: 508
Registered: April 2012
Location: India
Senior Member

Hi BlackSwan,

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.

Regards,
Nathan
Previous Topic: How do I return results based on 2 columns?
Next Topic: Invalid File Operation
Goto Forum:
  


Current Time: Sun Dec 28 19:09:08 CST 2014

Total time taken to generate the page: 0.10919 seconds