Home » SQL & PL/SQL » SQL & PL/SQL » Trigger on Materialized View (Oracle 10g,Windows XP)
Trigger on Materialized View [message #319458] Sun, 11 May 2008 10:04 Go to next message
yashora
Messages: 39
Registered: August 2006
Member
Hi All,

I am unable to come out of this issue for a quite long time and ofcourse i tried to find out the solution of my own, tried and tried and now in vain. Here are two schemas which are residing in the same database.

Schema_One@p5:

This contains two tables. Assume they are Emp and Dept. As and when the new records are created in the two tables(emp and dept) ,i need to pick only the columns Empno,Ename,Deptno and Dname.

Schema_Two@p5:

In this i have, a table "Emp_dept", as and when the new rows are added to the schema_One's Emp and Dept tables, those new rows should be recorded in Schema_Two's Emp_Dept table(Empno,Ename,Deptno and Dname).

Since the trigger allows to refer only one table, how can i achieve the above requirement?. For this i created a materialized view and tried to find the solution, but "no".


CREATE MATERIALIZED VIEW empdept_mv
PARALLEL BUILD IMMEDIATE 
REFRESH COMPLETE ON COMMIT AS 
SELECT EMP.EMPNO EMPNO,EMP.ENAME ENAME,DEPT.DEPTNO,DEPT.DNAME
FROM Schema_One.EMP.DEPTNO EMP,Schema_One.EMP.DEPTNO B
WHERE A.deptno=B.deptno;

Materialized View Created.



When the above is refreshed, i want to fire a trigger and captures the records into Schema_Two's emp_dept table.

Please let me know, why this is not getting refreshed? and why the trigger is not firing?

Regards,
Yashora

Sad Sad
Re: Trigger on Materialized View [message #319461 is a reply to message #319458] Sun, 11 May 2008 11:31 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8621
Registered: November 2002
Location: California, USA
Senior Member
Since you have the data that you want in the materialized view, what would be the point in duplicating that data in a table? It is generally a bad idea to create a trigger on a materialized view.
Re: Trigger on Materialized View [message #319467 is a reply to message #319458] Sun, 11 May 2008 13:05 Go to previous messageGo to next message
yashora
Messages: 39
Registered: August 2006
Member
Hi Barbara,

Thanks for the advice.Then how can i fulfill my task?

Regards,
Yashora
Re: Trigger on Materialized View [message #319480 is a reply to message #319467] Sun, 11 May 2008 19:01 Go to previous message
Barbara Boehmer
Messages: 8621
Registered: November 2002
Location: California, USA
Senior Member
yashora wrote on Sun, 11 May 2008 11:05
Hi Barbara,

Thanks for the advice.Then how can i fulfill my task?

Regards,
Yashora



What task? Why can't schema2 just select from the materialized view instead of an emp_dept table? If it is a question of names, then recreate the view with the name emp_dept or create a synonym. I am not seeing that there is any further task needing to be done.
Previous Topic: Understanding Document [PL/SQL]
Next Topic: improve the performance of procedure
Goto Forum:
  


Current Time: Sat Dec 03 03:37:32 CST 2016

Total time taken to generate the page: 0.13557 seconds