Re: Question about triggers

MAYEKEUL wrote:
> Hello everybody,
>
> Petite question existentielle sur les triggers avec oracle.
>
> I want to format my value before insert
> In this case, the value is a timestamp
>
> the table name is "MT_LONGTERM" and the fields are
>
> LT_TIMESTAMP - TIMESTAMP
> LT_VALUE - NUMBER(8,4)
>
> my f... trigger code ;o)
>
> create or replace trigger "MT_LONGTERM_T1"
> BEFORE
> insert on "MT_LONGTERM"
> for each row
> begin
> :NEW.LT_TIMESTAMP:=TO_TIMESTAMP(:NEW.LT_TIMESTAMP,'DD-MON-YYYY
> HH24:MI:SS');
> end;
>
> the reason is when I try to load a CSV file into this table, all date
> are refused
> Even if a try manually, line by line in sql
> .. Exept if I format the value
>
> So of course, my trigger do not work (:(((
>
> How can i catch my value before insert and apply the format?
>
>

You cannot "convert" the timestamp in the trigger, since the trigger already get's the value as a timestamp datatype - hence no need to do anything there.

What you need to do is, upon loading the data from the csv file, something like:
INSERT (..., LT_TIMESTAMP, LT_VALUE, ...) VALUES(..., TO_TIMESTAMP(:_timestamp_string_from_csv, '<your date format>'), TO_NUMBER(:_value_string_from_csv, '<your number format>'), ...);

Something along this lines - you need to convert the string value to a timestamp, but in the trigger you already have a timestamp value, it's too late there ... :)

cheers,
Martin Received on Tue Oct 10 2006 - 01:49:55 CDT