Hello

I raise this question on past and I try again maybe I'll find some
workarround.

I want to alter a column of type Timestamp to "Generate always as
current_timestamp".
Keep the field old values and impose the generation only after the
alteration.

Any suggestions will be appreciated
Thanks Sorin

Sorin

> I want to alter a column of type Timestamp to "Generate always as
> current_timestamp".
> Keep the field old values and impose the generation only after the
> alteration.
>
> Any suggestions will be appreciated

In this case can't you just use a default value?

John

Sorin

> I want to alter a column of type Timestamp to "Generate always as
> current_timestamp".
> Keep the field old values and impose the generation only after the
> alteration.
>

If there are no nulls then another option is to use an expression like

IF(Field1 IS NULL  THEN current_timestamp ELSE Field1)

John

Thanks John

> If there are no nulls then another option is to use an expression like
> IF(Field1 IS NULL  THEN current_timestamp ELSE Field1)

1. This raise an error "Expected timestamp constant..."
2. This will not solve the problem.
    The Field1 is a timestamp field I use to store the date and time that
record was last time updated.
    Until now the field get the value on the program level on the
BeforePost method.
    Now I want to set the value on the database level but keep the old
field values.
   On your suggestion the record field will not be updated

Thanks
Sorin

Sory no. 1 was my error.
Still it doesn't solve my problem

Thanks Sorin

> 1. This raise an error "Expected timestamp constant..."
> 2. This will not solve the problem.
>     The Field1 is a timestamp field I use to store the date and time that
> record was last time updated.
>     Until now the field get the value on the program level on the
> BeforePost method.
>     Now I want to set the value on the database level but keep the old
> field values.
>    On your suggestion the record field will not be updated

Perhaps for a little more flexibility and control, you should allow the
field to have NULLs, but write a Before-Insert Trigger instead:

 CREATE TRIGGER "CheckNullTimestamp" BEFORE INSERT
 ON "MyTable"
 WHEN
   NEWROW.Field1 IS NULL
 BEGIN
   SET NEWROW.Field1 = CURRENT_TIMESTAMP;
 END


David Cornelius
Cornelius Concepts

"Sorin H"  wrote in message
news:48695C29-0881-4E5C-965D-7B9A86AEC8D3@news.elevatesoft.com...

Sory no. 1 was my error.
Still it doesn't solve my problem

Thanks Sorin

> 1. This raise an error "Expected timestamp constant..."
> 2. This will not solve the problem.
>     The Field1 is a timestamp field I use to store the date and time that
> record was last time updated.
>     Until now the field get the value on the program level on the
> BeforePost method.
>     Now I want to set the value on the database level but keep the old
> field values.
>    On your suggestion the record field will not be updated

Thanks David

But no. It's important to know when the record was created or updated
so it can't have a null value.

> Perhaps for a little more flexibility and control, you should allow the
> field to have NULLs, but write a Before-Insert Trigger instead:
>
>  CREATE TRIGGER "CheckNullTimestamp" BEFORE INSERT
>  ON "MyTable"
>  WHEN
>    NEWROW.Field1 IS NULL
>  BEGIN
>    SET NEWROW.Field1 = CURRENT_TIMESTAMP;
>  END
>

Sorin

>      Until now the field get the value on the program level on the
> BeforePost method.
>      Now I want to set the value on the database level but keep the old
> field values.
>     On your suggestion the record field will not be updated

Generators only work when a record is created.  If you want the field to be
changed every time the record is updated you need a trigger like

CREATE TRIGGER "Set_Time" BEFORE UPDATE
ON "TableName"
BEGIN
 SET NewRow.Field1=current_timestamp;
END

John

Well, the trigger does exactly that: effectively prevents NULLs in that
field for all new records.

I'm not sure why the previously suggested default value wouldn't work.  But
you now have two possible ways to solve your problem.

If you also need to know when the record was updated, you can add a
BEFORE-UPDATE trigger.

--
David Cornelius
Cornelius Concepts


"Sorin H"  wrote in message
news:4F831AC8-04EA-43D4-AA27-8F682A185710@news.elevatesoft.com...

Thanks David

But no. It's important to know when the record was created or updated
so it can't have a null value.

> Perhaps for a little more flexibility and control, you should allow the
> field to have NULLs, but write a Before-Insert Trigger instead:
>
>  CREATE TRIGGER "CheckNullTimestamp" BEFORE INSERT
>  ON "MyTable"
>  WHEN
>    NEWROW.Field1 IS NULL
>  BEGIN
>    SET NEWROW.Field1 = CURRENT_TIMESTAMP;
>  END
>

Sorin,

<< I want to alter a column of type Timestamp to "Generate always as
current_timestamp".  Keep the field old values and impose the generation
only after the alteration. >>

There's really no way to do so, but I can see about adding a BY DEFAULT
option for generated expression columns also, similar to IDENTITY columns.

--
Tim Young
Elevate Software
www.elevatesoft.com