SPL and Triggers on the same table ?

Hi, I want to update a table using a trigger & procedure after an insert on the table. Here is the code : create procedure bomdecbug (parent char(22), child char(22), fromd date, till date) -- update bom. if from date > 01/01/1900 set from date = 01/01/1900 -- if to date < 14/10/2173 set to date = 14/10/2173 define flag integer; -- 1 todate bad, 2 fromdate bad , 3 both bad, 0 ok if (fromd > "01/01/1900") then let flag = 1; end if; if (till < "14/10/2173") then if (flag = 1) then let flag = 3; else let flag = 2; end if; end if; if (flag != 0) then if (flag = 1) then update bom set bomfrom = "01/01/1900" where bomparent = parent and bomchild = child; elif (flag = 2) then update bom set bomtill = "14/10/2173" where bomparent = parent and bomchild = child; else update bom set bomfrom = "01/01/1900", bomtill = "14/10/2173" where bomparent = parent and bomchild = child; end if; end if; end procedure; create trigger fixbombuginsert on bom referencing new as this for each row (execute procedure bomdecbug(this.bomparent,this.bomchild,this.bomfrom,this.bomtill) ); However I am getting error -747: -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. Which means you cannot update on the table you are triggering upon (i think!). Does anyone know a way around this ? Regards Richard.

You could use the EXECUTE PROCEDURE INTO clause in your trigger as follows CREATE PROCEDURE bomdecbug(fromd like bom.fromd, till like bom.till) returning date, date; if fromd > mdy(1,1,1900) then let fromd = mdy(1,1,1900); end if; if till < mdy(10,14,2173) then let till = mdy(10,14,2173); end if; return fromd, till; END PROCEDURE; create trigger tins_bom insert on bom referencing new as postfor each row when (post.fromd > mdy(1,1,1900) or post.till < mdy(10,14,2173)) (execute procedure bomdecbug(post.fromd, post.till) into fromd, till); Cheers, Rudy Richard wrote: > Hi, > > I want to update a table using a trigger & procedure after an insert on the > table. > > ... > However I am getting error -747: > -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. > > Which means you cannot update on the table you are triggering upon (i > think!). > > Does anyone know a way around this ? > > Regards > Richard.

Rudy Fernandes <rferdy@americasm01.nt.com> wrote in article <387B4923.D0E465FC@americasm01.nt.com>... > You could use the EXECUTE PROCEDURE INTO clause in your trigger .... This is all very well. But on my version of informix (5.10) you can only do this on an update trigger.... !! So , I'm stuck. You can't do an insert into another table, and trigger on that to do an update on theoriginal table (bom), because you would get error -747 again. Otherwise, it was a good idea. Thanks for your help. Richard.

i've had the same problem in the past but seeing your post made me try again to solve it. Reading from pg. 11-5 of informix guide to sql ( tutorial) 2nd edition it says when you define and update or select trigger you can name one or more columns in the table to activate the trigger. so INSERT is not mentioned, meaning there is NO WAY to do this without writing specialized routines to accomplish a work around. so here is one i wrote whose ideas you could try out as a work around... basically you create a table with the same schema as the table you want to finally insert and update, and it holds one row at a time, i call it temp_test in the following procedure: the other table, 'test' is some table i want to really insert into, and update a field. drop procedure ins_from_temp; create procedure ins_from_temp (){returning char(20), char(20), char(20);} define p1 like test.f1; define p2 like test.f2; define p3 like test.f3; foreach select f1,f2,f3 into p1,p2,p3 from temp_testlet p3 = 'new value of p3'; insert into test values ( p1,p2,p3);end foreach end procedure; then i created a trigger for the fake table, temp_test, to do the procedure when it is inserted... create trigger ins_temp_testinsert on temp_test for each row ( execute procedure ins_from_temp() ); so now each time you insert into the fake table you have a routine to play with to do your update algorithm, and the values you need to play with... hth Routine created. Richard wrote: > Hi, > > I want to update a table using a trigger & procedure after an insert on the > table. > > Here is the code : > > create procedure bomdecbug (parent char(22), child char(22), fromd date, > till date)> > -- update bom. if from date > 01/01/1900 set from date = 01/01/1900 > -- if to date < 14/10/2173 set to date = 14/10/2173 > > define flag integer; -- 1 todate bad, 2 fromdate bad , 3 both bad, 0 ok > > if (fromd > "01/01/1900") then > let flag = 1; > end if; > > if (till < "14/10/2173") then > if (flag = 1) then > let flag = 3; > else > let flag = 2; > end if; > end if; > > if (flag != 0) then > if (flag = 1) then > update bom set bomfrom = "01/01/1900" > where bomparent = parent and bomchild = child;> elif (flag = 2) then > update bom set bomtill = "14/10/2173" > where bomparent = parent and bomchild = child;> else > update bom set bomfrom = "01/01/1900", bomtill = "14/10/2173" > where bomparent = parent and bomchild = child;> end if; > end if; > end procedure; > > create trigger fixbombug> insert on bom > referencing new as this > for each row (execute procedure > bomdecbug(this.bomparent,this.bomchild,this.bomfrom,this.bomtill) ); > > However I am getting error -747: > -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. > > Which means you cannot update on the table you are triggering upon (i > think!). > > Does anyone know a way around this ? > > Regards > Richard.