Re: trigger on insert: updating one value of the same table

I am sorry, I do not understand this part: > insert into x values ( 1,"c=1+6=7",6); Can you explain a little bit more please? Thank you, On Fri, Dec 11, 2009 at 5:38 AM, Superboer <superboer7@t-online.de> wrote: > 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. > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

Hello insert into x values ( 1,"c=1+6=7",6); i insert into the view and i get a b c 1,"c=1+6=7",7 instead of a b c 1,"c=1+6=7",6 means that an insert can be bend so that a value for a column can be updated before it is inserted and if i understand your question properly that is what you were after. So col c is updated from 6 to 7 during the insert. again do not know if it is legal to change the table where the view is based on, could not find a thing rejecting this in the manual; maybe Madison or Jonathan can put some comments... Superboer. BTW just run the sql..... On 11 dec, 17:23, Gentian Hila <genti.t...@gmail.com> wrote: > I am sorry, I do not understand this part: > > > insert into  x values ( 1,"c=1+6=7",6);> > Can you explain a little bit more please? > > Thank you, > > On Fri, Dec 11, 2009 at 5:38 AM, Superboer <superbo...@t-online.de> wrote: > > 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-l...@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-l...@iiug.org > >http://www.iiug.org/mailman/listinfo/informix-list