Re: using timestamp in triggers

In article <emE4c.359$Ga1.244_at_newsfe3-win.server.ntli.net>, Eric Parker <eric.parkerthedross_at_virgin.net> wrote:
>> I have a table t1 (ts timestamp(6)) and t2 (ts timestamp)
>> and what I want to do in a trigger is essencially
>> on update or insert into t1
>> ...
>> INSERT into t2 VALUES (:new.ts);
>> ...
>> Except the value seems to fluxuate in the low (milli-second)
>> digits. I assume that this is similar to the restriction
>> that you can't access LONG datatype in triggers with :new
>> or :old.

Thanks for taking a look, Eric:

>My attempt at what I think you want to do :
>create table ts1 (a number, ts timestamp(6));
>create table ts2 (a number, ts timestamp);
>CREATE OR REPLACE TRIGGER TRG_TS1_1
>before update or insert
> on TS1
> for each row
>begin
> insert into ts2 values (:new.a, CURRENT_TIMESTAMP);
>end;

What I want to do is insert the same timestamp into ts2 that's going into ts1. The timestamp isn't "current_timestampt", it's something I get from another process.

So my body would be

   insert into ts2 values (:new.a, :new.ts);

I ran your example and everything worked as expected. I had used sysdate instead of current_timestamp, doing that now did not seem to make a difference. I may have been misreading the output data before... I'll double-check.

I did notice the other difference is that I was using an AFTER trigger rather than before. I can't imagine that would cause the different behavior though.

--
Asya Kamsky

In our society, you can state your views, but they have to be correct.
           ---  Ernie Hai, coordinator Singapore Gov't Internet Project