Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Timestamping records automatically

0 views
Skip to first unread message

Arch

unread,
Oct 5, 2007, 10:50:11 PM10/5/07
to
I need to add a date / time stamp to records as they are inserted into
a table. I need to do this at the server end.

Is there a "correct" way to do that? Should I simply use a default
value of sysdate? Or should this be done with a trigger or something
else?

Thanks for any advice

DA Morgan

unread,
Oct 6, 2007, 3:16:53 AM10/6/07
to

CREATE TABLE t (
col1 VARCHAR2(20),
col2 DATE DEFAULT SYSDATE);

INSERT INTO t
(col1)
VALUES
('AAA');

SELECT * FROM t;

Documented in Morgan's Library at www.psoug.org under HEAP TABLES.
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Arch

unread,
Oct 6, 2007, 4:09:26 PM10/6/07
to
On Sat, 06 Oct 2007 00:16:53 -0700, DA Morgan <damo...@psoug.org>
wrote:

>Arch wrote:
>> I need to add a date / time stamp to records as they are inserted into
>> a table. I need to do this at the server end.
>>
>> Is there a "correct" way to do that? Should I simply use a default
>> value of sysdate? Or should this be done with a trigger or something
>> else?
>>
>> Thanks for any advice
>
>CREATE TABLE t (
>col1 VARCHAR2(20),
>col2 DATE DEFAULT SYSDATE);
>
>INSERT INTO t
>(col1)
>VALUES
>('AAA');
>
>SELECT * FROM t;
>
>Documented in Morgan's Library at www.psoug.org under HEAP TABLES.

Thanks, Daniel. I had searched Morgan's library earlier but missed it
under Heap Tables.

Arch

unread,
Oct 7, 2007, 6:16:55 PM10/7/07
to
On Sat, 06 Oct 2007 00:16:53 -0700, DA Morgan <damo...@psoug.org>
wrote:

>Arch wrote:


>> I need to add a date / time stamp to records as they are inserted into
>> a table. I need to do this at the server end.
>>
>> Is there a "correct" way to do that? Should I simply use a default
>> value of sysdate? Or should this be done with a trigger or something
>> else?
>>
>> Thanks for any advice
>
>CREATE TABLE t (
>col1 VARCHAR2(20),
>col2 DATE DEFAULT SYSDATE);
>
>INSERT INTO t
>(col1)
>VALUES
>('AAA');
>
>SELECT * FROM t;
>
>Documented in Morgan's Library at www.psoug.org under HEAP TABLES.

I'd like to ask a follow up question if I may. If I wish to require
that default sysdate value and not permit the user to override it, is
a good approach to simply provide a view of the table without that
field?

Borrowing from Daniel's example:

CREATE TABLE t (
col1 VARCHAR2(20),

col2 VARCHAR2(20),
col3 DATE DEFAULT SYSDATE) ;

CREATE VIEW v AS (
SELECT col1, col2
FROM t ) ;

GRANT SELECT, INSERT ON v TO userperson ;

Is this a good solution?

Again, thanks for any advice

DA Morgan

unread,
Oct 7, 2007, 10:15:41 PM10/7/07
to

The only way to prevent user override is with a trigger.

0 new messages