Arch wrote:
> On Sat, 06 Oct 2007 00:16:53 -0700, DA Morgan <damorgan_at_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
The only way to prevent user override is with a trigger.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Sun Oct 07 2007 - 21:15:41 CDT
