Re: Help with triggers, please!

Troy, I think you can use a separate when condition in your trigger for each of your 5 tables. If I'm not wrong each when condition is independent of another, meaning that possibly all five when conditions will activate for one row. ---------------------------------------------------------------------- John H. Frantz Power-4gl: Extending Informix-4gl john@rl.is http://www.rl.is/~john/pow4gl.html Troy Peterson wrote: > > Let me explain my business problem and then show you my solution (which > doesn't work). > > I want to take ISAM databases and convert them into their normalized, > relational-world equivalent tables. I created 6 tables, one to emulate the > exact structure of the original ISAM db and five new relational tables with > constraints, etc. > I created an INSERT trigger on the ISAM-structured table so that each time a > row was inserted into it (LOADing from a delimited file) there would be a > corresponding insert into each of my relational tables. > > My first problem was that I had duplicate keys in one of the tables (which I > expected) and the load aborted. > Then I spent half a day figuring out how to use the "FOR EACH ROW WHEN" > structure to only insert when a record with the same key did not already > exist. > It worked when I updated a single table, but when I went to add another "FOR > EACH ROW" in the trigger it wouldn't allow it. I need to have specific > "WHEN" conditions for each of these tables because there is a potential for > duplicate keys in almost all of them. > > Can anyone explain a better approach, or perhaps clue me in on how to modify > my current approach. I would be most appreciative. Thank you for your > time. > > The following is my trigger code for updating one table (which works): > > CREATE TRIGGER createsqltables> INSERT ON prtded > REFERENCING NEW AS new > FOR EACH ROW > WHEN (NOT EXISTS (SELECT * FROM group > WHERE group.group_id = new.group_no)) > ( > INSERT INTO group (group_id) > VALUES (new.group_no) > );