Re: Dates in oracle 8i

From: David Fitzjarrell <oratune_at_msn.com>
Date: 5 Nov 2001 13:49:50 -0800
Message-ID: <32d39fb1.0111051349.37845f12@posting.google.com>

A 'blind' trigger that automatically populates the date field will produce such results. The original purpose for such a trigger is to eliminate the need to actually submit a date value for the insert, however it overwrites any submitted date values. A better trigger for such activity would be:

create or replace trigger gen_d1
before insert on t1
for each row
begin

     if :new.d1 is null then
          :new.d1 := sysdate;
     end if;

end;
/

This would allow d1 values to be provided in the insert statement, and also allow the database to provide d1 values should they be missing from the insert list.

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<1004964521.5106.0.nnrp-12.9e984b29_at_news.demon.co.uk>...
> Is it possible that your table has a pre-insert
> trigger that replaces the incoming value
> with SYSDATE; but no corresponding
> pre-update trigger ?
>
>
> --
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Host to The Co-Operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> Author of:
> Practical Oracle 8i: Building Efficient Databases
>
> Screen saver or Life saver: http://www.ud.com
> Use spare CPU to assist in cancer research.
>
> Antonio wrote in message
> <3604814d.0111050423.32fbf0c8_at_posting.google.com>...
> >Hi!
> >
> > I am making an insert from sqlplus into a table that have a date
> >column. I use the TO_DATE function like:
> > insert into t1 (v1,v2,d1) values (1,2,to_date('09/04/2001
> >10:23:08','DD/MM/YYYY HH24:MI:SS'));
> > Insert goes well excepting that the date inserted is the sysdate.
> >
> > v1 v2 d1
> >---------------------
> >1 2 05-NOV-2001
> >
> > If I make an update, it works well ...
> > update t1 set d1=to_date('09/04/2001 10:23:08','DD/MM/YYYY
> >HH24:MI:SS');
> >
> > v1 v2 d1
> >---------------------
> >1 2 09-APR-2001
> >
> > Somebody knows why it happens????
> >
> > Thanx in advance!
Received on Mon Nov 05 2001 - 15:49:50 CST