Message-Id: <10520.107915@fatcity.com>
From: Tony Guo <tonyg@actv.com>
Date: Wed, 7 Jun 2000 00:21:27 +0100
Subject: RE: Equivalent of Access autonumber

Or you can create a column with default value as sysdate: (...,UPDATED_AT
DATE    DEFAULT SYSDATE,...)

Tony Guo
DBA


-----Original Message-----
From: root@fatcity.com [mailto:root@fatcity.com]On Behalf Of Elliott,
Patrick
Sent: Tuesday, June 06, 2000 5:51 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Equivalent of Access autonumber


The rows are not automatically stamped with a time.  You will need to
implement a trigger on the table that populates a new DATE column with the
current date whenever an insert occurs on the table.

CREATE OR REPLACE TRIGGER <TRIGGER> BEFORE INSERT OR UPDATE ON <TABLE> FOR
EACH ROW
BEGIN
        :new.rec_create_dt := sysdate;
END;

If you need a number that has more precision than sysdate, you could use the
dbms_utility function called get_time which returns a number instead of a
date which is the number of 100ths of a second since the arbitrary epoch.
Just change rec_change_dt to a NUMBER column and substitute
dbms_utility.get_time() for sysdate in the above trigger.  You may need to
run $ORACLE_HOME/rdbms/admin/dbmsutil.sql first in order to access this
function.  I am also using 8.1.5, so I don't know if get_time is available
in previous releases.

The downfall of this solution is that it won't handle the records already in
the database.  If you are lucky then there is a very slim chance that you
could use the rowid in the order by, but there is no guarantee that the
results will be in date order.

> -----Original Message-----
> From:	John Dunn [SMTP:john.dunn@sefas.co.uk]
> Sent:	Tuesday, June 06, 2000 7:16 AM
> To:	Multiple recipients of list ORACLE-L
> Subject:	Equivalent of Access autonumber
>
> This is a developer question that I am passing on :
>
> Can Oracle have a autonumber like in Access?
>
> > I need to be able to list rows (processing_history records) in the order
> > in which they were created. A date/time stamp doesn't seem to be
> accurate
> > enough.
> >
> I don't think rowid is the answer - it seems to be to do with where data
> is
> stored.
>
>
> --
> Author: John Dunn
>   INET: john.dunn@sefas.co.uk
>
> Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California        -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
--
Author: Elliott, Patrick
  INET: Patrick.Elliott@bestbuy.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may