--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> At 08:13 AM 27/08/2003 +0000, you wrote:
> >Hi
> >
> >I have a table (FB 1.5 RC4) where a timestamp field with
> >default 'now' value shows always current time (or sometimes a time
> >little bit in the past) in some rows. When I requery data, the time
> >is new. It is NOT defined as a computed field.
> >
> >currently the table looks like this:
> >CREATE TABLE "CULLING_REASONS"
> >(
> > "CODE" NUMERIC(2, 0) NOT NULL,
> > "DESCRIPTION" VARCHAR(50) NOT NULL,
> > "CHANGE_DT" TIMESTAMP default 'now' NOT NULL,
> > PRIMARY KEY ("CODE")
> >);
> >
> >But at first I didn't created it that way. First it didn't
> >have "default" on "change_dt" field . I inserted data and later
> >added "default" with a tool that probably tweaked system tables
> >directly. (I don't remember if the "not null" was also added later
> >along with "default", probably not) And now the "change_dt" keeps
> >showing always current time. I updated the value on some rows and
> >then the value remained fixed on those rows.
> >
> >I have no proble with that (if I update and fix those strange
> >fields), just thought someone might be interested that such

behaviour

> >can happen. Unfourtunately I don't remember exactly how that table
> >structure evolved, maybe my explanation leads to wrong path.
>
> You don't understand what DEFAULT is intended to do. It is written

only

> once - at INSERT time - and then ONLY if the column is not included

in the

> field-list.
>
> If you want this timestamping capability, that you mistakenly

expected to

> get from your DEFAULT constraint, you need to write BEFORE INSERT

and

> BEFORE UPDATE triggers to do it.
>
> heLen

My example was a little misleading. I do have the triggers to update
this "change_dt" whenever a row is changed. And I meant
this "default" to store current date when inserting a row without
providing "change_dt".

But the problem is that "change_dt" changes each time I SELECT some
rows (or at least seems to be changing, as if it was "computed by
(cast ('now' as timestamp))".

Aivar