RE: a how can I do this in Informix question
- From: "Everett Mills" <eemills@xxxxxxxxxxxxxxxx>
- Date: Fri, 28 Oct 2005 10:35:35 -0500
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@xxxxxxxxxx]
> Sent: Friday, October 28, 2005 7:43 AM
> To: Quetzalcoatl
> Cc: informix-list@xxxxxxxx; owner-informix-list@xxxxxxxx
> 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@xxxxxxxx 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
.
- Prev by Date: Re: ER problem---CDRACK cause rootserver crash
- Next by Date: Re: Cannot put Server Online in Informix 7.31
- Previous by thread: Re: a how can I do this in Informix question
- Next by thread: RE: a how can I do this in Informix question
- Index(es):
Relevant Pages
|
