date format in a Trigger

Hello everybody,

today I Ihave found a problem with the date format in my Before Insert Trigger. In the Trigger I want to update a field called RES_LastChange its datatype is date. In the Trigger I use a SQL statement. It is something like

    v_sql:= 'Update Resourcen Set ... RES_LastChange= ''' || sysdate || ''' where RES_ID= ' || v_id;

    Execute Immediate v_sql;

After this Update the date is stored like dd.mm.yy.

When I try to make a select statement with to_char([Fieldname], 'dd.mm.yy hh24:mi:ss) I receive

    dd.mm.yy 00:00:00

Now it seems that I'm not able to store the time of updating.

It didn't work with

    v_sql:= 'Update Resourcen Set ... RES_LastChange= ''' || to_date(sysdate, 'dd.mm.yy hh24:mi:ss') || ''' where RES_ID= ' || v_id;

    Execute Immediate v_sql;
too

With

    :new.RES_LastChange:= sysdate;
I have no problems to store the time, but in my case it is not possible to store the time in this way.

Can anybody solve this problem and tell me who.

Best regards

Jochen Received on Wed Dec 13 2000 - 17:35:32 CET