Re: First attempt at a trigger

Jason Bodnar wrote: > > I'm making my first attempt at a trigger. > > I have a table called rest_zips with three fields (name, zip, area). I > have a second table called zip_areas (zip, area). > > Whenever a record is inserted into rest_zips I want to look the zip > code up in zip_areas and insert the appropriate area into rest_zips. > > I created the following trigger: > > CREATE TRIGGER zip2area> INSERT ON rest_zip > REFERENCING NEW AS post_ins > FOR EACH ROW (INSERT INTO rest_zip (area) SELECT area FROM zip_areas > WHERE zip = post_ins.zip) > > The trigger is created okay but when I try inserting something into > rest_zips as so: > > insert into rest_zips (name, zip) values ("jason", "78746")> > I get an error: 747: Table or column matches object referenced in > triggering statement. > > Is there no way to have a trigger act on its own table? INSERT triggers cannot act on the triggering table. This limitation exists in 5.x and I believe all later versions of Informix. For what it's worth, UPDATE triggers *can* act on the triggering table as long as they don't operate on the triggering columns. ----------- Roger Tomas AG Communication Systems