database trigger

[scheler]

hello all,
I created a table using "select statement" whcih is based on 3 different table. If a table is created in this way then that table will have data at that instance. If there is any new data added to the underlying tables then my table will not pick those records or data.
I need my table to refresh its data daily. So how do I achive this?

Can I write a database trigger to achive this. this trigger should refresh data in my new table on daily basis.


My table query was

Create table MY_TABLE as(select a.col1, a.col2, b.col5 from
table1 a, table2 b);
So my_table will have only data when this table was created but if there are new records in TABLE1 or TABLE2 then MY_TABLE will not pick those records until I run this query again. So I want to automate this process of creating this table as the data needs to be refreshed every now and then.

Any suggestions how to achiv ethis goal.

[stecal]

Do you know what a view is?

[marist89]

Or a materialized view?

[scheler]

Ofcourse I know what a view is. I don't want to create a view as I can't update the columns. I need to update a column after some process is done.

I was wondering about materialzed views MV. How to create those, can I achive what I need in MV. I need to update a column and also refresh the data every now and then.

[stecal]

And why is it you think a view won't work when data is added to an underlying/base table?

SQL> create or replace view dbaspt as
2 select ename, e.deptno, loc
3 from emp e, dept d
4 where e.deptno=d.deptno;

View created.

SQL> select * from dbaspt
2 where rownum < 5;

ENAME DEPTNO LOC
---------- ---------- -------------
SMITH 20 DALLAS
ALLEN 30 CHICAGO
WARD 30 CHICAGO
JONES 20 DALLAS

SQL> update dept
2 set loc='DENVER'
3 where deptno=20;

1 row updated.

SQL> select * from dbaspt
2 where rownum<5;

ENAME DEPTNO LOC
---------- ---------- -------------
SMITH 20 DENVER
ALLEN 30 CHICAGO
WARD 30 CHICAGO
JONES 20 DENVER


SQL> insert into emp values
2 (7999, 'SCHELER', 'DBA',7782,sysdate,1,null,20);

1 row created.

SQL> select * from dbaspt where ename='SCHELER';

ENAME DEPTNO LOC
---------- ---------- -------------
SCHELER 20 DENVER

[marist89]

http://www.oracle-base.com/Articles/...eplication.asp

[scheler]

I know it works in a view , but I don't want to create a view as I need to update a column, thats the reason I created a table so that I can update. If I create a table the drawback is it won't bring the most recent data as my table was created based on some query, to bring the mosst recetn data I have to execute the script that creates the my table which is what I want to automate.

[scheler]

marist89,

Let me ask you this, can we update a column in a materialized views.

[stecal]

to bring the mosst recetn data I have to execute the script that creates the my table which is what I want to automate.


...that creates your table how often? You want to automate this table creation based on DML on one or more of the base tables? So why would you want to be able to insert something into this table? Sounds like it is going to be re-created whenever a base table is changed. Is that correct, based on what you said above?

Your table is based on other tables. Yet, you want to be able to update a column in the new table. How does this updated record correspond to the data it was originally based on? And when the base table is changed, you need to have the new table updated to reflect the change. What kind of data model is this????

[marist89]

SQL> create materialized view xyz_mv
  2  parallel build immediate refresh complete
  3  as select x, y, z from xyz;

Materialized view created.

SQL> select * from xyz_mv;

         X Y                   Z
---------- ------------------- --------------------
         1 10/21/2002 15:52:28 foo
         2 10/21/2002 15:52:51 bar

SQL> update xyz_mv set z = 'xxx' where x=1;
update xyz_mv set z = 'xxx' where x=1
       *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

I'm not sure of why you think you need to update the mview. Maybe you might want to have another table linked to the mview that you can update and use as a filter condition. To be honest, without really knowing what you're trying to do, I find it hard to help.