Re: TimeStamp DataType??

From: Hakan <heren_at_home.com>
Date: 2000/07/20
Message-ID: <39764B54.46E87081@home.com>#1/1

hi,

I assume I understood. Let me try to answer the originator's question. ;-)

There is not this kind of functionality in Oracle. But you can do it either using AUDIT command or constraints/triggers. AUDIT is much more security related.

I prefer to use constraints and triggers if i need to. I'll try to explain
by examples.

For insert you can define a default value for the column like this:

SQLWKS> create table temp
     2> (data varchar2(10),
     3>  username varchar2(30) default user,
     4>  date_inserted date default sysdate)
     5> /

Statement processed.
SQLWKS> insert into temp(data) values('test')

     2> /
1 row processed.
SQLWKS> commit
Statement processed.

SQLWKS> /
SQLWKS> select * from temp
     2> /
DATA       USERNAME                       DATE_INSERTED       
---------- ------------------------------ --------------------
test       NVC                            07/19/2000 20:25:47 

1 row selected.

if there is an 'update' process then you need to write a trigger:

SQLWKS> alter table temp add
     2> (date_modified date)
     3> /

Statement processed.

SQLWKS> create or replace trigger test_trigger
     2> before update on temp
     3> for each row
     4> begin
     5>  :new.date_modified := sysdate;
     6> end;
     7> /

Statement processed.

SQLWKS> update temp
     2>  set username = 'test'
     3> /

1 row processed.
SQLWKS> commit
Statement processed.

SQLWKS> /
SQLWKS> select * from temp
     2> /
DATA       USERNAME                       DATE_INSERTED       
DATE_MODIFIED       
---------- ------------------------------ --------------------
--------------------
test       test                           07/19/2000 20:25:47 

07/19/2000 20:27:55
1 row selected.
SQLWKS> who updated? ok you can add user_modified to the table.

regards

Hakan

Robert William Vesterman wrote:
>
> On Wed, 19 Jul 2000 21:18:44 +0100, "Jim Harrison" <jim_at_colway.net>
> wrote:
>
> >DATE IS a time stamp column. To prove this to yourself try something like
> >this:
> >SELECT TO_CHAR(date_col, 'DD/MM/YYYY HH24:MI:SS') FROM your_tab;
>
> There seems to be some confusion between the original poster and the
> respondants. The confusion here lies in the fact that the
> Transact-SQL "timestamp" type has nothing to do with dates or times.
>
> What it is is a column which is automatically set by the server
> whenever an insert or update is done on the table it's in; its value
> is a monotonically increasing number, eight bytes in length.
>
> So, you can use it to determine the order that rows were modified
> (well, at least until quintillions of inserts/updates have been done
> on that table). Oracle's "date" type doesn't handle this, since its
> resolution is only down to the second. Moreover, Oracle's "date" is
> not an automatic thing - you have to specify it in the insert/update
> (or in a trigger, or whatever).
>
> So, is there anything in Oracle similar to timestamp?
>
> Bob Vesterman.
Received on Thu Jul 20 2000 - 00:00:00 CDT