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?
>
>
>
> Anyone can help me?
>
> Thanks
>
> Mayekeul
>
> Ps: sorry for my bad english, I am too bad!! :(

A trigger isn't going to help you here. How exactly are you loading the CSV file? Using SQL*Loader? If so, you can specify the input timestamp format in sqlldr control file. Check the docs for exact syntax and examples. Otherwise you need to use explicit TO_TIMESTAMP call with correct format. Or you can set the default format using ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD-MON-YYYY HH24:MI:SS' command.

Hth,

    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)     http://www.dynamicpsp.com Received on Tue Oct 10 2006 - 01:57:57 CDT