Re: TIMESTAMP in field during INSERT operation only

First of all, datetime values in Oracle are stored in special native format, the way they are presented to the user is up to the application. Oracle provides special conversion function, TO_CHAR(), for presenting datetime values according to the format string specified to this function. So your requirement to have timestamp in form of HH:MM:SS (as well as in just about any other form you may think of) should be implemented on application level (unless the underlying column data type is VARCHAR2, I'll cover this case later.)

Now to the task. Yes, this can be implemented through BEFORE INSERT OR UPDATE trigger FOR EACH ROW. Below is a sketch of such trigger, hope you will be able to adapt it for your needs:

CREATE OR REPLACE TRIGGER TRG$R_BIU$MYTABLE BEFORE INSERT OR UPDATE OF TIMESTAMP_COLUMN ON MYTABLE
FOR EACH ROW
BEGIN
 IF INSERTING THEN
   :NEW.TIMESTAMP_COLUMN := SYSDATE;

• or, for the case of TIMESTAMP_COLUMN being VARCHAR2
• :NEW.TIMESTAMP_COLUMN := TO_CHAR(SYSDATE,'HH24:MI:SS'); ELSIF UPDATING THEN
• disallow updates to TIMESTAMP_COLUMN RAISE_APPLICATION_ERROR(-20000, 'Update of TIMESTAMP_COLUMN is not allowed.'); END IF; END; /

Note that triggers may be disabled by their owners. To avoid this, you should leverage Oracle's built-in security model, so that table and trigger owner is not the same as inserters/updaters and the latter are granted just enough privileges to be able to alter table data, but not its definition.

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Ton" <76434.1353_at_compuserve.com> wrote in message
news:4477604.0301200031.18f8984b_at_posting.google.com...


> Kenneth,

>


> I think we have an misunderstanding:

>


> - I want to add a timestamp into field B with the format (HH:MM:SS)

> - This has to done automatically by the database during an INSERT and

> is not    allowed to change anymore during UPDATE operartion.

> - I do not want to change any current application so this has to be

> performed on a database level and not by an program /application.

>


> What I need is the right syntax to set this up. (maybe insert before

> trigger??)

> Anyway thanks for the earlier response you send me.

>
>
>


> > 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