update area with sql code

[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...area_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

[LKBrwn_DBA]

If your updates/inserts are on the base table, you have to create the trigger on that table.
Else if all inserts/updates are performed through the 'view', the you have to create the trigger as 'instead of ...'

[LKBrwn_DBA]

Also, you coould put these statements into a procedure and schedule with DBMS_JOB to execute at certain time of the day:

Update (select u.today u_today
, u.area_old u_area_old
, u.area_new u_area_new
, u.diff_area u_diff_area
, l.today l_today
, l.area l_area
from upd_area u, log_area l
where u.cod_id = l.cod_id)
set u_today = l_today
, u_area_old = u_area_new
, u_diff_area = u_area_new - l_area
, u_area_new = i_area
;
Insert into upd_area
select cod_id
, name
, description
, today
, 0 area_old
, area
, area
from log_area
where cod_id not in (
select cod_id from upd_area)
;

[raf]

LKBrwn_DBA thanks for your answer:
but I have any problem with you code.
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