RE: Help - Trigger issue

Actually this is the expected behavior. The first insert never actually completes until all triggered actions complete. Therefore the engine has to backtrack all the way back out of the insert action. I don't know if there's a version out that allows this, but obviously yours does not. The only way I have been able to get around this is as follows: Insert into your second table a row with the primary key from the first table, or add a field to the first table, something like 'updatedchar(1)'. Put a process into cron that runs every few minutes. This process will read from the second table, or from the first table where updated is null. Then simply have this process look at all records it finds, and update them accordingly. If the update is successful, you can delete from the second table (or of course you could have updated the 'updated' field to 'Y") It's a bit cumbersome, but I'm unaware of any alternative. Maybe someone else can tell us if there's a new version that allows this. (7.3 does not) HTH -----Original Message----- From: Jeff McFee [SMTP:jeffm@lysander.co.uk] Posted At: Friday, May 14, 1999 3:05 PM Posted To: Informix Conversation: Help - Trigger issue Subject: Re: Help - Trigger issue We are on Informix IDS V7.24. We have tried inserting into one table, triggering a procedure that inserts into another which triggers a procedure that performs an update to the original table. This also fails with the same error! Jeff McFee In article <7hhmoc$gbj$1@nnrp1.deja.com>, John Bejarano <jbejaran@my-dejanews.com> wrote: > In article <7hgsi0$s8p$1@nnrp1.deja.com>, > jeffm@lysander.co.uk wrote: > > Have a problem I could really use help with. > > > > A third party application INSERTS a row into a table - we have NO way > > of controlling what is INPUT. > > > > We need, on insert, to check a couple of details on the record and > > update one of the other columns (within the same record). > > > > The problem of course is that we then get a "-747: Table or column > > matches object referenced in triggering statement." error. > > > > This error effectively says that we cannot update the record that was > > the triggering action. > > > > We have tried synonyms, views and even creating a table to insert the > > record keys and new column value into then try and do it in the AFTER > > section of the trigger. ALL have exactly the same error. Nesting > > procedures/triggers fails as well. > > > > Hope someone somewhere can help on what I feell is probably a fairly > > common requirement. > > > > Thanks > > > > Jeff McFee > > > > --== Sent via Deja.com http://www.deja.com/ ==-- > > ---Share what you know. Learn what you don't.--- > > > > Have you tried having the INSERT trigger on the main table perform an > INSERT (incorporating your business logic) into a staging table, and > placing an INSERT trigger on that staging table to update the original > table? I don't know if separating it into two tables with separate > triggers will help or not, but you might give it a try. > > Kind regards, > > -- > <><><><><><><> > John Bejarano > San Mateo, CA > <><><><><><><> > > --== Sent via Deja.com http://www.deja.com/ ==-- > ---Share what you know. Learn what you don't.--- > --== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.---