RE: triggers

Hi all, I did some testing with triggers and stored procedures..... but I get a syntax error. My code: create trigger <trig1> update of <dbs_field1> on table <tbl1> referencing old as orig new as chng for each row when( (orig.dbs_field1 != chng.dbs_field1) ) ( after (execute procedure proc1(par1,par2)) A syntax error has occured ); What is my problem? Thanks for any tips. Met vriendelijke groeten / Sincères salutations / Kinds regards, Danny De Koster FIDELITY SOFT nv Cardijnstraat 5 1980 Eppegem Tel: 015/618.270 Fax: 015/616.052 -----Oorspronkelijk bericht----- Van: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Namens Art Kagel Verzonden: woensdag 17 maart 2010 22:20 Aan: ids@iiug.org Onderwerp: Re: triggers [19354] Do the testing and inserting in a stored procedure and call the procedure/function from the ON EVERY ROW clause in the trigger. If you have a later release of IDS you can define the routine as a "trigger routine" then the NEW record will be available in the procedure/function without explicitely passing in the columns: create function check_and_insert_a_copy() referencing NEW as latest for table1 returning int; ..... end function. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Mar 17, 2010 at 5:08 PM, DANNY DE KOSTER <ddk@fidelity-soft.be>wrote: > Hi all, > > I need some tips about triggers and inserting something in a table on > condition. > I created a trigger: > > create trigger trig1 > insert into table1 referencing new as new > > for each row( > > select from table2 where table2.id = new.id > > if table2.xx = 1 then > > insert into table3 values(xx) > > end if > > ); > > So I would like to test something in the trigger before I write something > in a > table. Is that possible?? > > thanks for any tips! > Danny > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001517588b7a5f0006048205ac28 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Try this: create trigger <trig1> update of <dbs_field1> on table <tbl1> referencing old as orig new as chng for each row when( orig.dbs_field1 != chng.dbs_field1) ( execute procedure proc1(par1,par2) ); Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Sun, Mar 21, 2010 at 12:38 PM, Danny DE KOSTER <ddk@fidelity-soft.be>wrote: > Hi all, > > I did some testing with triggers and stored procedures..... but I get a > syntax > error. > > My code: > > create trigger <trig1> > > update of <dbs_field1> > > on table <tbl1> > > referencing old as orig new as chng > > for each row when( > > (orig.dbs_field1 != chng.dbs_field1) > > ) > > ( > > after (execute procedure proc1(par1,par2)) > > A syntax error has occured > > ); > > What is my problem? Thanks for any tips. > > Met vriendelijke groeten / Sincères salutations / Kinds regards, > > Danny De Koster > > FIDELITY SOFT nv > > Cardijnstraat 5 > > 1980 Eppegem > > Tel: 015/618.270 > > Fax: 015/616.052 > > -----Oorspronkelijk bericht----- > Van: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Namens Art Kagel > Verzonden: woensdag 17 maart 2010 22:20 > Aan: ids@iiug.org > Onderwerp: Re: triggers [19354] > > Do the testing and inserting in a stored procedure and call the > > procedure/function from the ON EVERY ROW clause in the trigger. If you have > > a later release of IDS you can define the routine as a "trigger routine" > > then the NEW record will be available in the procedure/function without > > explicitely passing in the columns: > > create function check_and_insert_a_copy() referencing NEW as latest for> > table1 returning int; > > ...... > > end function. > > Art > > Art S. Kagel > > Advanced DataTools (www.advancedatatools.com) > > IIUG Board of Directors (art@iiug.org) > > See you at the 2010 IIUG Informix Conference > > April 25-28, 2010 > > Overland Park (Kansas City), KS > > www.iiug.org/conf > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and > > do not reflect on my employer, Advanced DataTools, the IIUG, nor any other > > organization with which I am associated either explicitly, implicitly, or > by > > inference. Neither do those opinions reflect those of other individuals > > affiliated with any entity with which I am affiliated nor those of the > > entities themselves. > > On Wed, Mar 17, 2010 at 5:08 PM, DANNY DE KOSTER <ddk@fidelity-soft.be > >wrote: > > > Hi all, > > > > > > I need some tips about triggers and inserting something in a table on > > > condition. > > > I created a trigger: > > > > > > create trigger trig1> > > insert into table1 referencing new as new> > > > > > for each row( > > > > > > select from table2 where table2.id = new.id > > > > > > if table2.xx = 1 then > > > > > > insert into table3 values(xx)> > > > > > end if > > > > > > ); > > > > > > So I would like to test something in the trigger before I write something > > > in a > > > table. Is that possible?? > > > > > > thanks for any tips! > > > Danny > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > --001517588b7a5f0006048205ac28 > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000e0ce00a50ded95e04825288e5

Thanks Art! it worked. Met vriendelijke groeten / Sincères salutations / Kinds regards, Danny De Koster FIDELITY SOFT nv Cardijnstraat 5 1980 Eppegem Tel: 015/618.270 Fax: 015/616.052 -----Oorspronkelijk bericht----- Van: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Namens Art Kagel Verzonden: zondag 21 maart 2010 18:02 Aan: ids@iiug.org Onderwerp: Re: triggers [19367] Try this: create trigger <trig1> update of <dbs_field1> on table <tbl1> referencing old as orig new as chng for each row when( orig.dbs_field1 != chng.dbs_field1) ( execute procedure proc1(par1,par2) ); Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Sun, Mar 21, 2010 at 12:38 PM, Danny DE KOSTER <ddk@fidelity-soft.be>wrote: > Hi all, > > I did some testing with triggers and stored procedures..... but I get a > syntax > error. > > My code: > > create trigger <trig1> > > update of <dbs_field1> > > on table <tbl1> > > referencing old as orig new as chng > > for each row when( > > (orig.dbs_field1 != chng.dbs_field1) > > ) > > ( > > after (execute procedure proc1(par1,par2)) > > A syntax error has occured > > ); > > What is my problem? Thanks for any tips. > > Met vriendelijke groeten / Sincères salutations / Kinds regards, > > Danny De Koster > > FIDELITY SOFT nv > > Cardijnstraat 5 > > 1980 Eppegem > > Tel: 015/618.270 > > Fax: 015/616.052 > > -----Oorspronkelijk bericht----- > Van: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Namens Art Kagel > Verzonden: woensdag 17 maart 2010 22:20 > Aan: ids@iiug.org > Onderwerp: Re: triggers [19354] > > Do the testing and inserting in a stored procedure and call the > > procedure/function from the ON EVERY ROW clause in the trigger. If you have > > a later release of IDS you can define the routine as a "trigger routine" > > then the NEW record will be available in the procedure/function without > > explicitely passing in the columns: > > create function check_and_insert_a_copy() referencing NEW as latest for> > table1 returning int; > > ...... > > end function. > > Art > > Art S. Kagel > > Advanced DataTools (www.advancedatatools.com) > > IIUG Board of Directors (art@iiug.org) > > See you at the 2010 IIUG Informix Conference > > April 25-28, 2010 > > Overland Park (Kansas City), KS > > www.iiug.org/conf > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and > > do not reflect on my employer, Advanced DataTools, the IIUG, nor any other > > organization with which I am associated either explicitly, implicitly, or > by > > inference. Neither do those opinions reflect those of other individuals > > affiliated with any entity with which I am affiliated nor those of the > > entities themselves. > > On Wed, Mar 17, 2010 at 5:08 PM, DANNY DE KOSTER <ddk@fidelity-soft.be > >wrote: > > > Hi all, > > > > > > I need some tips about triggers and inserting something in a table on > > > condition. > > > I created a trigger: > > > > > > create trigger trig1> > > insert into table1 referencing new as new> > > > > > for each row( > > > > > > select from table2 where table2.id = new.id > > > > > > if table2.xx = 1 then > > > > > > insert into table3 values(xx)> > > > > > end if > > > > > > ); > > > > > > So I would like to test something in the trigger before I write something > > > in a > > > table. Is that possible?? > > > > > > thanks for any tips! > > > Danny > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > --001517588b7a5f0006048205ac28 > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000e0ce00a50ded95e04825288e5 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

The syntax diagrams a very confusing. Before the acquisition the Informix manuals had a better set of syntax diagrams. <sigh> Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Sun, Mar 21, 2010 at 3:17 PM, Danny DE KOSTER <ddk@fidelity-soft.be>wrote: > Thanks Art! it worked. > > Met vriendelijke groeten / Sincères salutations / Kinds regards, > > Danny De Koster > > FIDELITY SOFT nv > Cardijnstraat 5 > 1980 Eppegem > > Tel: 015/618.270 > Fax: 015/616.052 > > -----Oorspronkelijk bericht----- > Van: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Namens Art Kagel > Verzonden: zondag 21 maart 2010 18:02 > Aan: ids@iiug.org > Onderwerp: Re: triggers [19367] > > Try this: > > create trigger <trig1> > update of <dbs_field1> > on table <tbl1> > referencing old as orig new as chng > for each row when( orig.dbs_field1 != chng.dbs_field1) > ( execute procedure proc1(par1,par2) ); > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > IIUG Board of Directors (art@iiug.org) > > See you at the 2010 IIUG Informix Conference > April 25-28, 2010 > Overland Park (Kansas City), KS > www.iiug.org/conf > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and > do not reflect on my employer, Advanced DataTools, the IIUG, nor any other > organization with which I am associated either explicitly, implicitly, or > by > inference. Neither do those opinions reflect those of other individuals > affiliated with any entity with which I am affiliated nor those of the > entities themselves. > > On Sun, Mar 21, 2010 at 12:38 PM, Danny DE KOSTER <ddk@fidelity-soft.be > >wrote: > > > Hi all, > > > > I did some testing with triggers and stored procedures..... but I get a > > syntax > > error. > > > > My code: > > > > create trigger <trig1> > > > > update of <dbs_field1> > > > > on table <tbl1> > > > > referencing old as orig new as chng > > > > for each row when( > > > > (orig.dbs_field1 != chng.dbs_field1) > > > > ) > > > > ( > > > > after (execute procedure proc1(par1,par2)) > > > > A syntax error has occured > > > > ); > > > > What is my problem? Thanks for any tips. > > > > Met vriendelijke groeten / Sincères salutations / Kinds regards, > > > > Danny De Koster > > > > FIDELITY SOFT nv > > > > Cardijnstraat 5 > > > > 1980 Eppegem > > > > Tel: 015/618.270 > > > > Fax: 015/616.052 > > > > -----Oorspronkelijk bericht----- > > Van: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Namens Art Kagel > > Verzonden: woensdag 17 maart 2010 22:20 > > Aan: ids@iiug.org > > Onderwerp: Re: triggers [19354] > > > > Do the testing and inserting in a stored procedure and call the > > > > procedure/function from the ON EVERY ROW clause in the trigger. If you > have > > > > a later release of IDS you can define the routine as a "trigger routine" > > > > then the NEW record will be available in the procedure/function without > > > > explicitely passing in the columns: > > > > create function check_and_insert_a_copy() referencing NEW as latest for> > > > table1 returning int; > > > > ...... > > > > end function. > > > > Art > > > > Art S. Kagel > > > > Advanced DataTools (www.advancedatatools.com) > > > > IIUG Board of Directors (art@iiug.org) > > > > See you at the 2010 IIUG Informix Conference > > > > April 25-28, 2010 > > > > Overland Park (Kansas City), KS > > > > www.iiug.org/conf > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > > and > > > > do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other > > > > organization with which I am associated either explicitly, implicitly, or > > by > > > > inference. Neither do those opinions reflect those of other individuals > > > > affiliated with any entity with which I am affiliated nor those of the > > > > entities themselves. > > > > On Wed, Mar 17, 2010 at 5:08 PM, DANNY DE KOSTER <ddk@fidelity-soft.be > > >wrote: > > > > > Hi all, > > > > > > > > > > I need some tips about triggers and inserting something in a table on > > > > > condition. > > > > > I created a trigger: > > > > > > > > > > create trigger trig1> > > > > insert into table1 referencing new as new> > > > > > > > > > for each row( > > > > > > > > > > select from table2 where table2.id = new.id > > > > > > > > > > if table2.xx = 1 then > > > > > > > > > > insert into table3 values(xx)> > > > > > > > > > end if > > > > > > > > > > ); > > > > > > > > > > So I would like to test something in the trigger before I write > something > > > > > in a > > > > > table. Is that possible?? > > > > > > > > > > thanks for any tips! > > > > > Danny > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > --001517588b7a5f0006048205ac28 > > > > > > > > > > > ******************************************************************************* > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --000e0ce00a50ded95e04825288e5 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000e0cd1ff3ee288f30482548d70