Trigger question: data values in a AFTER clause
Jean Sagi asked how to access old and new values in the AFTER clause of an IDS 9.3/9.4 trigger, since using them there gave error 732. John Carlson suggested calling a stored procedure. rkusenet said it cannot be done and offered a workaround: initialise a variable or temp table in BEFORE, fill it in FOR EACH ROW, use it in AFTER. Art Kagel explained AFTER runs once after all rows, so no single row applies.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
It is possible to access the values from an update in an AFTER clause of a trigger? I could reference the old and new values with a FOR EACH ROW clause of a trigger (using REFERENCING OLD/NEW). But when I try to use the old or new values in an AFTER clause informix gives 732 error stating that old and new values only works in the scope of the for each row clause. So in the after clause how can I access the values that are being updated?, if it is possible of course (The manual is not very ilustrative on this theme). BTW: I'm using IDS 9.3/9.4 Chucho! Jean Sagi jeansagi@myrealbox.com jeansagi@yahoo.com sending to informix-list
On Thu, 23 Sep 2004 18:53:41 -0500, "Jean Sagi" <jeansagi@myrealbox.com> wrote: > > >It is possible to access the values from an update in an AFTER clause of a trigger? > >I could reference the old and new values with a FOR EACH ROW clause of a trigger (using REFERENCING OLD/NEW). > >But when I try to use the old or new values in an AFTER clause informix gives 732 error stating that old and new values only works in the scope of the for each row clause. > >So in the after clause how can I access the values that are being updated?, if it is possible of course (The manual is not very ilustrative on this theme). > >BTW: I'm using IDS 9.3/9.4 > Call a stored procedure, perhaps? JWC
"Jean Sagi" <jeansagi@myrealbox.com> wrote in message news:civsd8$c0g$1@news.xmission.com... > > > It is possible to access the values from an update in an AFTER clause of a trigger? > > I could reference the old and new values with a FOR EACH ROW clause of a trigger (using REFERENCING OLD/NEW). > > But when I try to use the old or new values in an AFTER clause informix gives 732 error stating that old and new values only works in the scope of the for each row clause. > > So in the after clause how can I access the values that are being updated?, if it is possible of course (The manual is not very ilustrative on this theme). > > BTW: I'm using IDS 9.3/9.4 You can not. To work with updated values means that you have to look into a set of data. Only FOR EACH ROW can do that. However, depending on the complexity you can do it as follows:- BEFORE initialize some variable/temp table FOR EACH ROW store the updated values in variable/temp table AFTER use the updated values/temp table to do the work. Definitely not elegant, but you can work with the set of data after it has been updated.
On Thu, 23 Sep 2004 19:53:41 -0400, Jean Sagi wrote: Think about it this way: The trigger is launched once per transaction that contains a triggering statement. The BEFORE block runs before any row is updated and the AFTER block is run after all rows have already been updated. Only the FOR EACH ROW block is executed when a particular row is being updated. So, what data would you like to see in the AFTER block, the first row's data or the last, or the Nth? Art S. Kagel > It is possible to access the values from an update in an AFTER clause of a > trigger? > > I could reference the old and new values with a FOR EACH ROW clause of a > trigger (using REFERENCING OLD/NEW). > > But when I try to use the old or new values in an AFTER clause informix > gives 732 error stating that old and new values only works in the scope of > the for each row clause. > > So in the after clause how can I access the values that are being updated?, > if it is possible of course (The manual is not very ilustrative on this > theme). > > BTW: I'm using IDS 9.3/9.4 > > Chucho! > > Jean Sagi > jeansagi@myrealbox.com > jeansagi@yahoo.com > > sending to informix-list