SOLVED !!!!! ** H E L P ** Auto inserting date/Time (SQL*FORMS 2.3)

In article <1993Jun24.115115.95915_at_embl-heidelberg.de> hazledine_at_embl-heidelberg.de writes:
>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.
>>
 [origial attempt deleted]
>
>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 ;
>

[alternative solution deleted]

>Hope this helps.
>

It did ! Using a slight modification to this I have achieved the desired effect. As given the piece of code (above) changed the time for all the records in the table. I used this in a POST-INSERT trigger to set the date correctly:-

update My_table

    set TiME=sysdate
    WHERE :block_name.ROWID = My_table.ROWID

This results in the Date and Time being stored in the TiME field of the record just inserted. It doesn't appear on the from, but that was never required anyway (I could of course use another step later on in the POST-INSERT trigger to put that time on the Form if I needed to.

        Many, many thanks to Hazeldine (sorry, I've lost the rest of your name) for solving this one for me.

        Thanks also to all those of you who have tried to help. I have had many suggestions that hinged around the syntax. The problem was really quite simple. DATE type fields on SQL*Forms 2.3 don't have a time part. No matter how hard you try, they cannot store a date. This means that any attempt to use a SELECT ... INTO TiME ... will fail, because once the data is put int a SQL*Forms DATE field, it loses the time part. (remember only SQL*Forms fields can be reference in an INTO clause.)

        I hope this has been as useful for you guys as it has for me. THe number of differebt variations that don't work are quite amazing, but they all fell down due to SQL*Forms DATE type not having any time component. I can't wait now till we upgrade to ORACLE v7.0 and I get to use Forms v3.0 with the DATETIME type.

        If anybody would like more information about this, please mail me.

                Thanks once again,

                        David McCann

                        (Relieved of UK) Received on Thu Jun 24 1993 - 18:43:06 CEST