Re: a how can I do this in Informix question
Hi.
I know a method, but maybe it's not very elegant.
You can use on a trigger an "execute function" and get returned
value into a column of the table.
Here you have a little example. My goal is enforce "value2" on table
to take the same value of "value1" on insert.
--## My prove-table
create table tmpomar (valor1 date, valor2 date);
--## A function which returns teh same.. sorry, I don't know a better way
create function sp_dummyomar (pD_Valor date)
returning date; return pD_Valor;
end function;
--## My trigger using my enforcing function
create trigger trgomar insert on tmpomarreferencing new as n
for each row
(
execute function sp_dummyomar(n.valor1) into valor2
);
-- ## Finally, when I execute this (today here is October 26th) ...
insert into tmpomar values (today-2, today);
insert into tmpomar values (today-4, today);
insert into tmpomar values (today-8, today);
--## I get this:
select * from tmpomar;
valor1 valor2
10/26/2005 10/26/2005
10/24/2005 10/24/2005
10/20/2005 10/20/2005
This can be done with update triggers also (beware with modify same
columns than triggering-columns)
So, If you create a function playing with CURRENT - YEAR - MONTH-
DAY functions as Martin said, you will have the behaviour you are looking
for.
By the way, I'm using IDS 9.30.UC6. More info at "Informix Guide to
SQL: Syntax" page 2-316 and so on.
Later
Omar Muqoz
----- Original Message -----
From: "Quetzalcoatl" <quetzalcoatl@pandora.be>
To: <informix-list@iiug.org>
Sent: Thursday, October 27, 2005 1:35 PM
Subject: a how can I do this in Informix question
> 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
sending to informix-list