Question on Updating columns on insert trigger of table

We have a table that when a new row is inserted, there are two columns that need data that will be generated from inserting into tables in another database. These other tables are inserted through a stored procedure triggered as a result of the insert into the originating table. Scenario: insert into TABLEA (column1,column2) values ("A","B") trigger "pssb".a_charge_insert insert on "pss".TABLEAreferencing new as post for each row ( execute procedure "pssb".charge_insert(post.ctrl_nbr ,post.bkg_nbr ,post .charge_type )); charge_insert executes procedure b; procedure b executes procedure c; procedure c inserts into 2 separate tables each of which returns a unique value. These two unique values need to be put into TABLEA.column3 and TABLEA.column4 Problem: TABLEA cannot be updated from within any of the stored procedures called as a result of the trigger as a 747 error results. INFORMIX ERROR BELOW. -747 Table or column matches object referenced in triggering statement. This error is returned when a triggered SQL statement acts on the triggering table, or when both statements are updates and the column being updated in the triggered action is the same as the column being updated by the triggering statement. --- Anyone have any suggestions? Thanks in advance. REX

>Subject: Question on Updating columns on insert trigger of table >From: rexarnold@aol.com (REXARNOLD) >Date: 11.03.00 00:33 W. Europe Standard Time >Message-id: <20000310183338.02640.00001094@ng-fh1.aol.com> > >We have a table that when a new row is inserted, there are two columns that >need data that will be generated from > inserting into tables in another database. These other tables are inserted >through a stored procedure triggered as a result of the insert into the >originating table. > >Scenario: >insert into TABLEA (column1,column2) values ("A","B")> trigger "pssb".a_charge_insert insert on "pss".TABLEA >referencing new as post for each row > > > ( > > > execute procedure "pssb".charge_insert(post.ctrl_nbr ,post.bkg_nbr >,post >.charge_type )); > > >charge_insert executes procedure b; >procedure b executes procedure c; >procedure c inserts into 2 separate tables each of which returns a unique >value. >These two unique values need to be put into TABLEA.column3 and TABLEA.column4 >Problem: > TABLEA cannot be updated from within any of the stored procedures called >as >a result of the trigger as a 747 error results. >INFORMIX ERROR BELOW. >-747 Table or column matches object referenced in triggering >statement. > >This error is returned when a triggered SQL statement acts >on the triggering table, or when both statements are updates >and the column being updated in the triggered action is the >same as the column being updated by the triggering >statement. >--- >Anyone have any suggestions? >Thanks in advance. >REX Do a distributed transaction instead without SPL. Or, Enterprise Replication. Nona

Watawinona wrote: > >Subject: Question on Updating columns on insert trigger of table > >From: rexarnold@aol.com (REXARNOLD) > >Date: 11.03.00 00:33 W. Europe Standard Time > >Message-id: <20000310183338.02640.00001094@ng-fh1.aol.com> > > > >We have a table that when a new row is inserted, there are two columns that > >need data that will be generated from > > inserting into tables in another database. These other tables are inserted > >through a stored procedure triggered as a result of the insert into the > >originating table. > > > >Scenario: > >insert into TABLEA (column1,column2) values ("A","B")> > trigger "pssb".a_charge_insert insert on "pss".TABLEA > >referencing new as post for each row > > > > > > ( > > > > > > execute procedure "pssb".charge_insert(post.ctrl_nbr ,post.bkg_nbr > >,post > >.charge_type )); > > > > > >charge_insert executes procedure b; > >procedure b executes procedure c; > >procedure c inserts into 2 separate tables each of which returns a unique > >value. > >These two unique values need to be put into TABLEA.column3 and TABLEA.column4 > >Problem: > > TABLEA cannot be updated from within any of the stored procedures called > >as > >a result of the trigger as a 747 error results. > >INFORMIX ERROR BELOW. > >-747 Table or column matches object referenced in triggering > >statement. > > > >This error is returned when a triggered SQL statement acts > >on the triggering table, or when both statements are updates > >and the column being updated in the triggered action is the > >same as the column being updated by the triggering > >statement. > > Do a distributed transaction instead without SPL. Or, Enterprise Replication. Hmm; would you care to elaborate on this answer? I can't think how either would help, but then I have been absent for a few days and my brain may have corroded during that time... The original question includes the statement: insert into TABLEA(column1,column2) values ("A","B") trigger "pssb".a_charge_insert insert on "pss".TABLEA referencing new as post for each row (execute procedure "pssb".charge_insert(post.ctrl_nbr, post.bkg_nbr, post.charge_type )); It isn't clear to me how the INSERT statement and the trigger code are related, especially since the column1, column2 don't appear in the trigger code. However, you can update columns in an inserted row where the user did not specify the value for that column. And it may even be possible in the latest server versions to do more than that. You don't tell us which version you're using, so it is hard to be more definitive. You might also want to consider whether you should reorganize the logic a bit. You might want to use a transaction (ideally a savepoint, but they aren't available to us), and insert the subsidiary rows before doing the main insert. If you want to conceal all this from the user, create a stored procedure which takes the data to be inserted into TableA and have it do the subsidiary inserts before doing the insert into TableA. You then deny everybody INSERT permissions on TableA so that the only way to get data into it is via the stored procedure. That won't work so well if you use ISQL Perform -- it has no clue about using a SP to do INSERT -- but is otherwise sound. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.95 -- see http://www.perl.com/CPAN #include <disclaimer.h>

You need to use the INTO clause in the SP called by the trigger. Here's an example (in a different context) create procedure junk_l2date ( l int ) returning date; return trunc((l/86400) + 25568 ); end procedure; create table junk ( collection_date char(20), collection_char char(20) ); CREATE TRIGGER junk_iINSERT ON junk referencing new as post FOR EACH ROW ( execute procedure junk_l2date (post.collection_date) into collection_char); In your scenario, it would mean that the values of column3 and column4 must migrate through RETURN statements from your chain of procedures back to charge_insert that places them INTO column3 and column4. Rudy REXARNOLD wrote: > We have a table that when a new row is inserted, there are two columns that > need data that will be generated from > inserting into tables in another database. These other tables are inserted > through a stored procedure triggered as a result of the insert into the > originating table. > > Scenario: > insert into TABLEA (column1,column2) values ("A","B") > trigger "pssb".a_charge_insert insert on "pss".TABLEA> referencing new as post for each row > > ( > > execute procedure "pssb".charge_insert(post.ctrl_nbr ,post.bkg_nbr > ,post > .charge_type )); > > charge_insert executes procedure b; > procedure b executes procedure c; > procedure c inserts into 2 separate tables each of which returns a unique > value. > These two unique values need to be put into TABLEA.column3 and TABLEA.column4 > ...