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_at_fatcity.com [mailto:root_at_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_at_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_at_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_at_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_at_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_at_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