[raf]

hi,
I've a view "SPACE" with this columns:

COD_ID...........VARCHAR2(32)
NAME.............VARCHAR2(32)
DESCRIPTION......VARCHAR2(64)
AREA.............NUMBER

AREA in this view change every day.
I'd like to create one procedures that takes the data from this view , fills a table it and does the difference between the current area (sysdate) and the area of the previous day.

FOR EXAMPLE:

VIEW SPACE:

COD_ID.......NAME.........DESCRIPTION........AREA. ......DATE_TODAY
101..........Jo............room.............1200.. .......01/07/03
105..........Tom...........building.........1900.. .......01/07/03


I'd like to create this table:

cod_id..name..description...today....area_old...ar ea_new..diff_area
101.......Jo.....room.......02/07/03...1200......2000.........800
105......Tom....building....02/07/03...1900......2000.........100


How can I create this table that update automatically the area?

I'm trying with trigger but it not run correctly

Thanks
Raf

[AlanP]

Try setting up an Oracle job (like crontab on unix) which you can set to run everyday at a set time and then get it to call a stored procedure which will compare and populate the two sets of data.

Alan

[raf]

I explain to you what I did in detail:

We suppose that my view's name is log_area (COD_ID, NAME, DESCRIPTION, AREA)

I created a table "audit_area" with this columns:
COD_ID
NAME
DESCRIPTION
AREA_OLD
AREA_NEW
DIFF_AREA

I Filled the table "audit_area" with this code:
insert into audit_area
select cod_imm, name, description, 0 area, 0 area, 0 area
from log_area;

Now I tried to run the code sql that you have written:

Update (select u.area_old u_area_old,
u.area_new u_area_new,
u.diff_area u_diff_area,
l.area l_area
from audit_area u, log_area l
where u.cod_id = l.cod_id)
set u_area_old = u_area_new,
u_diff_area = u_area_new - l_area,
u_area_new = l_area;

but I get this error:
ORA-01779: cannot modify a column which maps to a non key-preserved
table

What I wrong?
How can I resolve this problem?

Thanks
Raf