Re: using timestamp in triggers

From: Eric Parker <eric.parkerthedross_at_virgin.net>
Date: Sat, 13 Mar 2004 13:52:51 -0000
Message-ID: <emE4c.359$Ga1.244@newsfe3-win.server.ntli.net>

"Asya Kamsky" <asya_at_bayarea.net> wrote in message news:1055il6f541uhc1_at_corp.supernews.com...
> Now that I got my dumb syntax error out of my triggers they
> are working fine except for variables of type timestamp.
>
> 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.
>
> What I'm not sure of is what's the workaround?
>
> --
> Asya Kamsky
>
> In our society, you can state your views, but they have to be correct.
> --- Ernie Hai, coordinator Singapore Gov't Internet Project

Asya

Not sure the problem is you're describing 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;

INSERT INTO TS1 VALUES (1, CURRENT_TIMESTAMP); INSERT INTO TS1 VALUES (2, CURRENT_TIMESTAMP); COMMIT; SQL> SELECT * FROM TS1;

                    A
---------------------

TS

---

                    1

13-MAR-04 13.41.56.630000

                    2

13-MAR-04 13.42.07.666000 SQL> SELECT * FROM TS2;

                    A
---------------------

TS

---

                    1

13-MAR-04 13.41.56.640000

                    2

13-MAR-04 13.42.07.666000 HTH eric

-- 
Remove the dross to contact me directly

Received on Sat Mar 13 2004 - 07:52:51 CST