Hi Tom,

I have a table with the following SQL statement:

create table A2
(
REF VARCHAR2(400),
REC_FROM VARCHAR2(500),
SENT_TO VARCHAR2(500),
TD_DATE DATE,
DAY NUMBER,
ACTION VARCHAR2(500),
REMARKS VARCHAR2(2000),
ENTRY_NO NUMBER,
CHEK VARCHAR2(5)
)

Insert into A2 values ('A-25015','PO/APO','PMS',to_date('22-JAN-07','DD-MON-RR'),'CST',null,73264,null);
Insert into A2 values ('A-25015','PMS','PO/APO',to_date('24-JAN-07','DD-MON-RR'),'CST Checking',null,74923,null);
Insert into A2 values ('A-25015','PO/APO','SAO (LP)',to_date('07-FEB-07','DD-MON-RR'),'CST Checking',null,79302,null);
Insert into A2 values ('A-25015','SAO (LP)','Dir. Proc.',to_date('09-FEB-07','DD-MON-RR'),'Signature on CST',null,80407,null);
Insert into A2 values ('A-25015','Dir. Proc.','User Division',to_date('12-FEB-07','DD-MON-RR'),'Acceptance/Recommendation',null,81663,null);
Insert into A2 values ('A-25015','User Division','Dir. Proc.',to_date('20-FEB-07','DD-MON-RR'),'Accepted/Recommended',null,85744,null);

I would like to have either a database trigger or a form level trigger which would automatically populate the above mentioned Field 'DAY' with no of days which could be achieved by subtracting the old date from the new date(TD_DATE). I tried the following code, but it gives errors:

declare
a =date;
begin
if :system.cursor_record='1'
then
:day:= 0;
else
previous_record;
a:=:tran_date;
next_record;
:day:=:tran_date - :a;
end if;
end;


What would be best in terms of performance that we should define a database view which could have this code for the day field so that the number of days are populated accordingly. If we need to define a database level trigger then what could be the code, as we need to get the td_date value from the previous record and then subtract it from the current td_date value.

Another option could be to design a view which could be queried every time for a specific REF value.

TIA


Hassan

scott%ORA10GR2> select ename, hiredate, hiredate-lag(hiredate) over (order by hiredate) day from emp;

ENAME      HIREDATE         DAY
---------- --------- ----------
SMITH      17-DEC-80
ALLEN      20-FEB-81         65
WARD       22-FEB-81          2
JONES      02-APR-81         39
BLAKE      01-MAY-81         29
CLARK      09-JUN-81         39
TURNER     08-SEP-81         91
MARTIN     28-SEP-81         20
KING       17-NOV-81         50
JAMES      03-DEC-81         16
FORD       03-DEC-81          0
MILLER     23-JAN-82         51
SCOTT      09-DEC-82        320
ADAMS      12-JAN-83         34

14 rows selected.