Help - Trigger issue

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.---

Last time I checked, Informix did not support insert triggers that update the triggering table. However, I thought I read some time ago that this was going to be supported in a future version of Informix. You didn't say what version you're using, but you might check into a newer version. Roger Tomas AG Communication Systems 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.---

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.---

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.---

Hi I had the same problem, but I did find a work-around. Create a trigger that inserts the key values into a second table. The second table has an insert trigger that executes a SPL which runs a batch file. This batch file then runs another batch file with no-interruption and backgroup set, it is essential that this is done, as Informix will not let you do it from within SPL on the first batch file. This second batch file then sleeps for a short will, 1 second is usually sufficient, and then executes a second SPL to update the first table. The syntax here is not at all accurate, but is just the general principle. If you want the full syntax and examples let me know and I'll dig them out eg. Trigger 1: on insert T1, after insert into table T2 (post.key1, post.key2) Trigger 2: on insert T2, execute SPL1() SPL 1: SYSTEM "/scripts/batch_1" Batch_1: nohup /scripts/batch_2 & Batch_2: isql dbname << EOF execute procedure SPL2() EOF SPL2: curs1 select * from T2 update T1 set T1.a = T1.a * T1.b where current of curs1 Wayne Sheldon <jeffm@lysander.co.uk> wrote in message news:7hgsi0$s8p$1@nnrp1.deja.com... > 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.---