Cannot modify values of insert trigger

I need to be able to modify the address fields on a record inserted into an order table. I have an insert trigger on the table which calls a SPL to modify the address fields. This returns an error -747 (Table or column matches object referenced in triggering statement.) Does anyone have a work around. Sent via Deja.com http://www.deja.com/ Before you buy.

robjmoore@my-deja.com wrote: > I need to be able to modify the address fields on a record inserted > into an order table. > > I have an insert trigger on the table which calls a SPL to modify the > address fields. This returns an error -747 (Table or column matches > object referenced in triggering statement.) > > Does anyone have a work around. > > Sent via Deja.com http://www.deja.com/ > Before you buy. Actually, you can modify values of an INSERT trigger. Here's an example that does it : CREATE PROCEDURE junk_nulls(l_test_id int, l_prd_cd char(2)) RETURNING char(1); IF l_test_id < 5 THEN RETURN "A"; ELSE RETURN "B"; END IF; END PROCEDURE; create table junk ( test_id serial not null , prd_cd char(2) not null ); create trigger tins_junk insert on junk referencing new as postfor each row when (post.prd_cd is null) (execute procedure junk_nulls(post.test_id, post.prd_cd) into prd_cd); insert into junk values (0,null); insert into junk values (0,null); insert into junk values (0,null); insert into junk values (0,null); insert into junk values (0,null); insert into junk values (0,'C'); insert into junk values (0,null); select * from junk; drop table junk; drop PROCEDURE junk_nulls;

Rudy Fernandes wrote: > robjmoore@my-deja.com wrote: > > I need to be able to modify the address fields on a record inserted > > into an order table. > > > > I have an insert trigger on the table which calls a SPL to modify the > > address fields. This returns an error -747 (Table or column matches > > object referenced in triggering statement.) > > > > Does anyone have a work around. > > Actually, you can modify values of an INSERT trigger. Here's an example > that does it : > > CREATE PROCEDURE junk_nulls(l_test_id int, l_prd_cd char(2)) > RETURNING char(1);> IF l_test_id < 5 THEN > RETURN "A"; > ELSE > RETURN "B"; > END IF; > > END PROCEDURE; > > create table junk > ( > test_id serial not null , > prd_cd char(2) not null > );> > create trigger tins_junk insert on junk referencing new as post> for each row > when (post.prd_cd is null) > (execute procedure junk_nulls(post.test_id, post.prd_cd) into > prd_cd); > insert into junk values (0,null); > insert into junk values (0,null); > insert into junk values (0,null); > insert into junk values (0,null); > insert into junk values (0,null); > insert into junk values (0,'C'); > insert into junk values (0,null); > select * from junk; > drop table junk; > drop PROCEDURE junk_nulls; Which version of the server are you using, Rudy? Only the more recent versions allow a trigger to override the value specified in the VALUES list of an INSERT statement -- I'm not sure of the details, but 5.08.UD1 OnLine (don't ask why) does not allow, whereas 7.31.UC1 and 9.21.UC1 do (testing on Solaris 7). -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"

I'm currently using v7.31UC2 & v9.20UC1 on HP10.20. I'm reasonably certain that this worked on v7.30uc3 on Solaris7, too. Rudy Jonathan Leffler wrote: > Which version of the server are you using, Rudy? Only the more recent > versions allow a trigger to override the value specified in the VALUES > list of an INSERT statement -- I'm not sure of the details, but 5.08.UD1 > OnLine (don't ask why) does not allow, whereas 7.31.UC1 and 9.21.UC1 do > (testing on Solaris 7). > > -- > Yours, > Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> > Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN > "I don't suffer from insanity; I enjoy every minute of it!"