Re: Help with triggers, please!

If you have a new enough version of Informix (7.2 something I think) it seems to me that the simplest solution would be to use a violation table. All inserts that would result in a duplicate for a unique index would then automatically go to the violation table and you could deal with them after the load. You would then need no when clause or other checking in your trigger. Check the Informix Guide to SQL: Syntax manual available at http://www.informix.com/answers under the "start violations table" and "set" statements for further information. On Wed, 12 Aug 1998 14:14:04 -0700, "Troy Peterson" <tp096255@gte.net> 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) > );> > > > Nils Myklebust NM Data AS Norway E-mail: Nils.Myklebust@nmdata.com FAQ at: http://www.iiug.org/techinfo/faq/faq_top.html (Now with ODBC info under "Third party products".)