Hello,

I have a problem in Informix that I need to solve.

I have table

Table ABC
{
Field1 integer,
Field2 char(5),
Insdate integer
}

What I want now is when an insert occurs on this table on the fields
(Field1 and Field2) that afterwards the fields Insdate is filled with
the date of today but stored as a number in the format YYYYMMDD.

Example:

Insert into ABC (Field1, Field2)
values (1, "Wim");

then should the query

Select * from ABC;

return:

Field1 Field2 Insdate
1 Wim 20051027

My collegue found how to do it in Oracle and now I want to prove it also
can be done in Informix. I hope. Else me and my big mouth :-)

So help me please.

Thanks,

Wim

The way to do this best in Informix is to redfine insdate as a DATE
datatype set a default for the column = TODAY

Set DBDATE = Y4MD



Quetzalcoatl wrote:
> Hello,
>
> I have a problem in Informix that I need to solve.
>
> I have table
>
> Table ABC
> {
> Field1 integer,
> Field2 char(5),
> Insdate integer
> }
>
> What I want now is when an insert occurs on this table on the fields
> (Field1 and Field2) that afterwards the fields Insdate is filled with
> the date of today but stored as a number in the format YYYYMMDD.
>
> Example:
>
> Insert into ABC (Field1, Field2)
> values (1, "Wim");
>
> then should the query
>
> Select * from ABC;
>
> return:
>
> Field1 Field2 Insdate
> 1 Wim 20051027
>
> My collegue found how to do it in Oracle and now I want to prove it also
> can be done in Informix. I hope. Else me and my big mouth :-)
>
> So help me please.
>
> Thanks,
>
> Wim

Quetzalcoatl wrote:
> Hello,
>
> I have a problem in Informix that I need to solve.
>
> I have table
>
> Table ABC
> {
> Field1 integer,
> Field2 char(5),
> Insdate integer
> };

> What I want now is when an insert occurs on this table on the fields
> (Field1 and Field2) that afterwards the fields Insdate is filled with
> the date of today but stored as a number in the format YYYYMMDD.

How about this as just ONE way to do it:

create procedure create_abc_default_date() returning int
define result int;

select year(current) * 10000 + month(current) * 100 + day(current)
into result
from systables
where tabid = 1;

return result;
end procedure;

create trigger abc_ins for insert on abc
referencing new as rec
for each row
update abc set insdate = create_abc_default_date()
where field1 = rec.field1 and field2 = rec.field2;

Art S. Kagel

> Example:
>
> Insert into ABC (Field1, Field2)
> values (1, "Wim");
>
> then should the query
>
> Select * from ABC;
>
> return:
>
> Field1 Field2 Insdate
> 1 Wim 20051027
>
> My collegue found how to do it in Oracle and now I want to prove it also
> can be done in Informix. I hope. Else me and my big mouth :-)
>
> So help me please.
>
> Thanks,
>
> Wim

Thanks a lot.
This is what I was looking for.

W

Art S. Kagel wrote:
> Quetzalcoatl wrote:
>
>> Hello,
>>
>> I have a problem in Informix that I need to solve.
>>
>> I have table
>>
>> Table ABC
>> {
>> Field1 integer,
>> Field2 char(5),
>> Insdate integer
>> };
>
>
>> What I want now is when an insert occurs on this table on the fields
>> (Field1 and Field2) that afterwards the fields Insdate is filled with
>> the date of today but stored as a number in the format YYYYMMDD.
>
>
> How about this as just ONE way to do it:
>
> create procedure create_abc_default_date() returning int
> define result int;
>
> select year(current) * 10000 + month(current) * 100 + day(current)
> into result
> from systables
> where tabid = 1;
>
> return result;
> end procedure;
>
> create trigger abc_ins for insert on abc
> referencing new as rec
> for each row
> update abc set insdate = create_abc_default_date()
> where field1 = rec.field1 and field2 = rec.field2;
>
> Art S. Kagel
>
>> Example:
>>
>> Insert into ABC (Field1, Field2)
>> values (1, "Wim");
>>
>> then should the query
>>
>> Select * from ABC;
>>
>> return:
>>
>> Field1 Field2 Insdate
>> 1 Wim 20051027
>>
>> My collegue found how to do it in Oracle and now I want to prove it
>> also can be done in Informix. I hope. Else me and my big mouth :-)
>>
>> So help me please.
>>
>> Thanks,
>>
>> Wim