Re: TIMESTAMP in field during INSERT operation only

On Sun, 19 Jan 2003 13:03:36 GMT, mokat67_at_hotmail.com wrote:

>I need the following which should be a very easy question for most of
>you:
>
>I have a table A with field B. I want to have a timestamp (Format
>(HH:MM:SS) in field B. The timestamp may only be insert into field B
>during an INSERT and not being changed anymore during an UPDATE.
>
>Can someone give me the right syntax. Is this done by an INSERT BEFORE
>trigger?
>Thanks
>

Hi,

To ensure that current timestamp is inserted on INSERT :

Create table A(c1 number(10) ,B date default SYSDATE);

Then :
insert into A(c1) values (1);

OR
insert into A values (1,DEFAULT);

Of course this does not itself prevent programmers from issuing inserts like
insert into A(c1) values (1,SYSDATE-1000);

or updates like

update A set B = sysdate + 1000 where.....;

In both cases you need a trigger to prevent it, because Oracle provides no functionality of defining a column value as CONSTANT once inserted. A check constraint is not capable of this either, as it does not accept SYSDATE expressions.

• Kenneth Koenraadt

To ensure that this field is Received on Sun Jan 19 2003 - 09:41:39 CST