Re: ** H E L P ** Auto inserting date/Time (SQL*FORMS 2.3)

cs91dnm_at_brunel.ac.uk (David N McCann) writes:

> Hi,
> I'm going bersek here. All I want to do is have a form insert the date
> and time into a DATE column in a table when the new record is commited.
>
> My best attempt yet has this in a PRE-INSERT trigger:-
>
> select to_date(to_char(sysdate,'hh24:mi:ss dd-mon-yy'),
> 'hh24:mi:ss dd-mon-yy')
> INTO :time
> FROM dual
>
> where :time is the field on the form (and in the table) that I want the
> date and time stored in. Unfortunately this ALWAYS results in the time part
> being set to 00:00:00

I'd suggest using a POST-INSERT trigger to update the column in the table after the new row has been inserted, rather than the PRE-INSERT trigger which is updating the field on the form. Something like:

UPDATE	MYTABLE
SET	TIME_COL = SYSDATE
WHERE	ROWID = :MYTABLE.ROWID ;

I think (though I'm not 100% sure) that SQL*Forms V2 does have the rowid available in a POST-INSERT trigger. If that doesn't work, you would have to specify the row you want to update by using data values from fields on the form, as in "WHERE COL_A = :MYTABLE.COL_A".

Hope this helps.

David Hazledine                                                EMBL Data Library
Database Administrator                                                PF 10.2209
EMBL Data Library                                       6900 Heidelberg, Germany

Internet: Hazledine_at_EMBL-Heidelberg.DE