SEO

#1 (**permalink**) 04-20-2008, 09:28 AM

Snake, et. al.:
How about something like this:

CREATE TRIGGER ins_abc_trg
INSERT ON abc REFERENCING NEW AS abc_ins

FOR EACH ROW
(EXECUTE PROCEDURE stp_ins_abc (abc_ins.field1, abc_ins.field2,
abc_ins.ins_date) INTO insdate);

CREATE PROCEDURE stp_ins_abc(f1 INTEGER, f2 CHAR(5), ins_dt INTEGER)

DEFINE now_yr SMALLINT;
DEFINE now_mth SMALLINT;
DEFINE now_dy SMALLINT;

IF ( ins_date IS NOT NULL AND ins_date <> 0 ) THEN
RETURN ins_dt;
END IF;

--assumes date format "mm/dd/yyyy"

LET ins_dt = 0;

LET now_yr = YEAR(TODAY);
LET now_mth = MONTH(TODAY);
LET now_dy = DAY(TODAY);

LET ins_dt = (now_yr * 10000) + (now_mth * 100) + now_dy;

RETURN ins_dt;

END PROCEDURE;

Now you can beat your Urkle friend over the head with it.

--EEM

> -----Original Message-----
> From: Martin Fuerderer [mailto:MARTINFU@de.ibm.com]
> Sent: Friday, October 28, 2005 7:43 AM
> To: Quetzalcoatl
> Cc: informix-list@iiug.org; owner-informix-list@iiug.org
> Subject: Re: a how can I do this in Informix question
>
> Hi "feathered snake",

>
> I think you want to look into "Triggers"
> (see "Guide to SQL: Tutorial") and in the
> trigger you probably want to make use of
> function "CURRENT" which gives you the
> current date (and time). You then can
> combine this with functions "YEAR",
> "MONTH" and "DAY" to somehow produce
> your desired integer and in the trigger update
> the column in the just inserted row.
>
> I'm not so much an expert in SQL statements,
> so you've to find out the details yourself (or
> from someone more SQL literate than me).
>
> [ Quetzalcoatl was worshipped by the Aztecs.
> It is derived from Quetzal (hummingbird) and
> Coatl (snake) of Nahuatl (ancient Indian language
> in the region of Central America). E.g. see
> http://home.arcor.de/mfu1/quetzalcoatl.jpg
> ]
>
> Regards,
> Martin
> --
> Martin Fuerderer
> IBM Informix Development Munich, Germany
> Information Management
>
> owner-informix-list@iiug.org wrote on 27.10.2005 20:35:58:
>
> > 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
sending to informix-list

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

RE: a how can I do this in Informix question