multiple statements in a trigger

hello! i would like to have two stored proc's fire in one stored procedure (insert proc) is this possible in informix? this is the syntax i have now but it is incorrect : CREATE TRIGGER "informix"."itCCDjob" INSERT ON "informix"."job" REFERENCING NEW AS NEW FOR EACH ROW( EXECUTE PROCEDURE "informix"."ipCCDjob" ( NEW.id, NEW.type_cid, NEW.status_cid, NEW.cust_role_id, NEW.rc_role_id, NEW.batch_date, NEW.username, NEW.jobdesc, NEW.run_day_of_week, NEW.run_date ) (EXECUTE PROCEDURE "informix".tsp_iud_job(17,NEW.id,1399) ); thanks for any help! tom

tomL wrote: > hello! > i would like to have two stored proc's fire in one stored procedure (insert proc) > is this possible in informix? > this is the syntax i have now but it is incorrect : > > CREATE TRIGGER "informix"."itCCDjob" > INSERT ON "informix"."job" > REFERENCING NEW AS NEW FOR EACH ROW( EXECUTE PROCEDURE > "informix"."ipCCDjob" > ( NEW.id, > NEW.type_cid, > NEW.status_cid, > NEW.cust_role_id, > NEW.rc_role_id, > NEW.batch_date, > NEW.username, > NEW.jobdesc, > NEW.run_day_of_week, > NEW.run_date ) > (EXECUTE PROCEDURE "informix".tsp_iud_job(17,NEW.id,1399) ); > > thanks for any help! > > tom You forgot to put the comma separating the two procedures CREATE TRIGGER "informix"."itCCDjob" INSERT ON "informix"."job" REFERENCING NEW AS NEW FOR EACH ROW( EXECUTE PROCEDURE "informix"."ipCCDjob" ( NEW.id, NEW.type_cid, NEW.status_cid, NEW.cust_role_id, NEW.rc_role_id, NEW.batch_date, NEW.username, NEW.jobdesc, NEW.run_day_of_week, NEW.run_date ) , (EXECUTE PROCEDURE "informix".tsp_iud_job(17,NEW.id,1399) );

rkusenet <rkusenet@sympatico.ca> wrote in message news:<c104ju$1bsrjk$1@ID-75254.news.uni-berlin.de>... > tomL wrote: > > hello! > > i would like to have two stored proc's fire in one stored procedure (insert proc) > > is this possible in informix? > > this is the syntax i have now but it is incorrect : > > > > CREATE TRIGGER "informix"."itCCDjob" > > INSERT ON "informix"."job" > > REFERENCING NEW AS NEW FOR EACH ROW( EXECUTE PROCEDURE > > "informix"."ipCCDjob" > > ( NEW.id, > > NEW.type_cid, > > NEW.status_cid, > > NEW.cust_role_id, > > NEW.rc_role_id, > > NEW.batch_date, > > NEW.username, > > NEW.jobdesc, > > NEW.run_day_of_week, > > NEW.run_date ) > > (EXECUTE PROCEDURE "informix".tsp_iud_job(17,NEW.id,1399) ); > > > > thanks for any help! > > > > tom > > You forgot to put the comma separating the two procedures > > CREATE TRIGGER "informix"."itCCDjob" > INSERT ON "informix"."job" > REFERENCING NEW AS NEW FOR EACH ROW( EXECUTE PROCEDURE > "informix"."ipCCDjob" > ( NEW.id, > NEW.type_cid, > NEW.status_cid, > NEW.cust_role_id, > NEW.rc_role_id, > NEW.batch_date, > NEW.username, > NEW.jobdesc, > NEW.run_day_of_week, > NEW.run_date ) > , > (EXECUTE PROCEDURE "informix".tsp_iud_job(17,NEW.id,1399) ); ++++++++++++++++++++++++++++++ i have tried several variations, including this one and all come back with syntax errors in dbaccess. i called support and they do not believe it is possible. variation #1 - CREATE TRIGGER "informix"."itCCDjob" INSERT ON "informix"."job" REFERENCING NEW AS NEW FOR EACH ROW( EXECUTE PROCEDURE "informix"."ipCCDjob" ( NEW.id, NEW.type_cid, NEW.status_cid, NEW.cust_role_id, NEW.rc_role_id, NEW.batch_date, NEW.username, NEW.jobdesc, NEW.run_day_of_week, NEW.run_date ) , (EXECUTE PROCEDURE "informix".tsp_iud_job(17,NEW.id,1399) ); variation #2 - CREATE TRIGGER "informix"."itCCDjob" INSERT ON "informix"."job" REFERENCING NEW AS NEW FOR EACH ROW( EXECUTE PROCEDURE "informix"."ipCCDjob" ( NEW.id, NEW.type_cid, NEW.status_cid, NEW.cust_role_id, NEW.rc_role_id, NEW.batch_date, NEW.username, NEW.jobdesc, NEW.run_day_of_week, NEW.run_date ) , EXECUTE PROCEDURE "informix".tsp_iud_job(17,NEW.id,1399) );

tomL wrote: > ++++++++++++++++++++++++++++++ > i have tried several variations, including this one and all come back > with syntax errors in dbaccess. i called support and they do not > believe it is possible. I have gotten the following script to work on 9.40.TC2: drop table trig_test; drop table trig_log; drop procedure logger1; drop procedure logger2; create table trig_test ( col1 serial, col2 char(1) ); create table trig_log ( col1 serial, col2 char(80) ); create procedure logger1 (p_num integer, p_char char(1)) insert into trig_log values (p_num, 'The value I got was '|| p_char);end procedure; create procedure logger2 (p_num integer, p_char char(1)) insert into trig_log values (p_num, 'The value I got was still'||p_char); end procedure; create trigger tt_test insert on trig_test referencing new as new for each row ( execute procedure logger1 (new.col1, new.col2), execute procedure logger2 (new.col1, new.col2) ); insert into trig_test (col2) values ('a'); insert into trig_test (col2) values ('b'); select * from trig_log; -- returns 4 rows, as I would expect