RE: multiple statements in a trigger
You need to create the table and procedures first, then the trigger.
What is your syntax message (of variation 2)? Engine version? Do you have an owner mismatch? (Remove the owners).
MW
> -----Original Message-----
> From: owner-informix-list@iiug.org
> [mailto:owner-informix-list@iiug.org]On Behalf Of tomL
> Sent: Friday, 20 February 2004 2:50 a.m.
> To: informix-list@iiug.org
> Subject: Re: multiple statements in a trigger
>
>
> 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) );
sending to informix-list
THANK YOU ALL VERY MUCH! it is working like a charm now..... tom