trigger on insert: updating one value of the same table

I have a program that is closed to us. It inserts a new row, every column of the table. I want one to update the value of one column on the inserted row. I tried a INSERT on trigger but since the column is included on the insert it gives me the error: "table or column matches object referenced in triggering statement". Is there a way that I can achieve this through a trigger? That would be extremely helpful. Thank you,

In article <mailman.372.1260477541.6236.informix-list@iiug.org>, Gentian Hila says... > >I have a program that is closed to us. It inserts a new row, every >column of the table. > >I want one to update the value of one column on the inserted row. > >I tried a INSERT on trigger but since the column is included on the >insert it gives me the error: > >"table or column matches object referenced in triggering statement". > >Is there a way that I can achieve this through a trigger? > >That would be extremely helpful. > >Thank you, can you put the sql you used to accomplish what you want. I know Informix supports updating the same col of the table where the trigger is. The trick is to use stored procedure with into clause. Sample code pasted below (no testing, I am giving it off my hat) CREATE TRIGGER TESTINSERT ON YOUR_TABLE referencing new as n old as o foreach row execute procedure yourproc() into n.yourcol Here yourcol is the column you would want to update. You have to use a sproc for this which will throw back the value you want to update with. Sproc will make it slow, but have no choice. Once again the above code is untested and I am extrapolating what I did with UPDATE TRIGGER. Please post the result of your testing.

Dono if it is legal... should read the manual better however: change your table to be a view; say your table was called x: then rename x to xtab.... ex: create table xtab ( a int, b char(10), c int ); create view x as select * from xtab; CREATE PROCEDURE tessie (myaval INT, mybval char(10) , mycval int ) insert into xtab values ( myaval, mybval, mycval + myaval);END PROCEDURE; CREATE TRIGGER insertxv INSTEAD OF INSERT ON x REFERENCING NEW AS n FOR EACH ROW (EXECUTE PROCEDURE tessie(n.a , n.b , n.c )); insert into x values ( 1,"c=1+6=7",6); select * from xtab; select * from x; Superboer. dba6...@gmail.com schreef: > In article <mailman.372.1260477541.6236.informix-list@iiug.org>, Gentian Hila > says... > > > >I have a program that is closed to us. It inserts a new row, every > >column of the table. > > > >I want one to update the value of one column on the inserted row. > > > >I tried a INSERT on trigger but since the column is included on the > >insert it gives me the error: > > > >"table or column matches object referenced in triggering statement". > > > >Is there a way that I can achieve this through a trigger? > > > >That would be extremely helpful. > > > >Thank you, > > can you put the sql you used to accomplish what you want. > > I know Informix supports updating the same col of the table > where the trigger is. The trick is to use stored procedure > with into clause. > > Sample code pasted below (no testing, I am giving it off my hat) > > CREATE TRIGGER TEST> INSERT ON YOUR_TABLE > referencing new as n old as o > foreach row > execute procedure yourproc() into n.yourcol> > Here yourcol is the column you would want to update. > You have to use a sproc for this which will throw back > the value you want to update with. Sproc will make it > slow, but have no choice. > > Once again the above code is untested and I am extrapolating > what I did with UPDATE TRIGGER. > > Please post the result of your testing.

I did a test on this as the whole SQL is a long thing and has other parts in there that would simply make it more confusing. 1) Create a test table create table test1 (cust_num CHAR(10), desc CHAR(30), val INTEGER) 2) Created a procedure that returns an integer create procedure p_test1() RETURNING INTEGERreturn 100; END PROCEDURE 3) Tried to create a trigger just like you suggest: CREATE TRIGGER t_test1INSERT ON test1 referencing new as new for each row (execute procedure p_test1() INTO new.val); but it gives me an error: Incorrect use of old or new values correlation name inside trigger. So I guess this can be done on update but not on insert Thanks for your suggestion. On Thu, Dec 10, 2009 at 5:46 PM, <dba6319@gmail.com> wrote: > In article <mailman.372.1260477541.6236.informix-list@iiug.org>, Gentian Hila > says... >> >>I have a program that is closed to us. It inserts a new row, every >>column of the table. >> >>I want one to update the value of one column on the inserted row. >> >>I tried a INSERT on trigger but since the column is included on the >>insert it gives me the error: >> >>"table or column matches object referenced in triggering statement". >> >>Is there a way that I can achieve this through a trigger? >> >>That would be extremely helpful. >> >>Thank you, > > can you put the sql you used to accomplish what you want. > > I know Informix supports updating the same col of the table > where the trigger is. The trick is to use stored procedure > with into clause. > > Sample code pasted below (no testing, I am giving it off my hat) > > CREATE TRIGGER TEST> INSERT ON YOUR_TABLE > referencing new as n old as o > foreach row > execute procedure yourproc() into n.yourcol> > Here yourcol is the column you would want to update. > You have to use a sproc for this which will throw back > the value you want to update with. Sproc will make it > slow, but have no choice. > > Once again the above code is untested and I am extrapolating > what I did with UPDATE TRIGGER. > > Please post the result of your testing. > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >