Need some trigger help

I have one trigger defined like following create trigger "scorpion".tupd_bll_edit_map update of eff_date, dis_date,emp_id,aty_cd,tna_dtime on "scorpion".bll_edit_map referencing old as pre new as post for each row when (((select count(*) from "scorpion".bll_edit_map x0 where ((((x0.bll_mth_id = post.bll_mth_id ) AND (x0.edit_id = post.edit_id ) ) AND (x0.eff_date < post.dis_date ) ) AND (x0.dis_date > post.eff_date ) ) ) > 1. ) ) (execute procedure "scorpion".user_exception(338 )), ( insert into st3hist:"scorpion".bll_edit_map (bll_mth_id, edit_id,prd_cd,eff_date,dis_date,emp_id,aty_cd,tna_dtime) values (pre.bll_mth_id ,pre.edit_id ,pre.prd_cd ,pre.eff_date ,pre.dis_date ,pre.emp_id ,pre.aty_cd ,pre.tna_dtime )); As per my understanding, It should do insert when the 'where' condition is false BUT It's not doing this. It is executing the stored procedure for all kind of condition whether it is true or false. Is there any syntax error in definition b'coz I was not able to find out any example in manual like this. Meanwhile my table definition is following create table "scorpion".bll_edit_map ( bll_mth_id integer not null disabled , edit_id integer not null disabled , prd_cd char(2) not null disabled , eff_date date not null disabled , dis_date date not null disabled , emp_id integer not null disabled , aty_cd char(1) not null disabled , tna_dtime datetime year to fraction(5) not null disabled ) extent size 16 next size 16 lock mode page; I will highly appreciate for any help in this. Thanks

Sanjeev Sagar wrote: > I have one trigger defined like following > > create trigger "scorpion".tupd_bll_edit_map update of eff_date, > dis_date,emp_id,aty_cd,tna_dtime on "scorpion".bll_edit_map > referencing old as pre new as post > for each row > when (((select count(*) from "scorpion".bll_edit_map > x0 where ((((x0.bll_mth_id = post.bll_mth_id ) AND (x0.edit_id = > post.edit_id ) ) AND (x0.eff_date < post.dis_date ) ) AND > (x0.dis_date > > post.eff_date ) ) ) > 1. ) ) > (execute procedure "scorpion".user_exception(338 )), > ( > insert into st3hist:"scorpion".bll_edit_map (bll_mth_id, > edit_id,prd_cd,eff_date,dis_date,emp_id,aty_cd,tna_dtime) values > (pre.bll_mth_id ,pre.edit_id ,pre.prd_cd ,pre.eff_date ,pre.dis_date> > ,pre.emp_id ,pre.aty_cd ,pre.tna_dtime )); > > As per my understanding, It should do insert when the 'where' condition > is false BUT > It's not doing this. It is executing the stored procedure for all kind > of condition whether it is true or false. Hi Sanjeev (ex-Datamas, I presume), Actually, your update trigger definition indicates that (a) The Stored procedure user_exception fires conditional to the "when" clause. (b) The "insert into st3hist" fires for every row, unconditionally. The brackets (so many of them!) are probably leading to the confusion. If you look carefully at the definition (use % in vi), you will find that the "execute procedure" clause belongs to the "when" condition, while the "insert into st3hist" is an independent action which occurs "for each row". If you want the "insert int st3hist" clause to be conditional to the "when", group the "execute" and the "insert" using a pair of brackets (and remove all the redundant brackets while you're at it :-)) Rudy

No. The unqualified, or nconditional, action (ie the insert) is executed for every row. To make it conditional you need another WHEN clause testing the negation of the condition in the first since there is no otherwise clause. In fact a particular row CAN satisfy multiple conditions and so trigger multiple WHEN action clauses. The WHEN is NOT a C switch or 4GL CASE statement. It is more like sequential if {} blocks followed by an unconditional {} block than an if{} followed by none or more else if{} blocks followed by an else clause. Art S. Kagel Sanjeev Sagar wrote: > > I have one trigger defined like following > > create trigger "scorpion".tupd_bll_edit_map update of eff_date, > dis_date,emp_id,aty_cd,tna_dtime on "scorpion".bll_edit_map > referencing old as pre new as post > for each row > when (((select count(*) from "scorpion".bll_edit_map > x0 where ((((x0.bll_mth_id = post.bll_mth_id ) AND (x0.edit_id = > post.edit_id ) ) AND (x0.eff_date < post.dis_date ) ) AND > (x0.dis_date > > post.eff_date ) ) ) > 1. ) ) > (execute procedure "scorpion".user_exception(338 )), > ( > insert into st3hist:"scorpion".bll_edit_map (bll_mth_id, > edit_id,prd_cd,eff_date,dis_date,emp_id,aty_cd,tna_dtime) values > (pre.bll_mth_id ,pre.edit_id ,pre.prd_cd ,pre.eff_date ,pre.dis_date> > ,pre.emp_id ,pre.aty_cd ,pre.tna_dtime )); > > As per my understanding, It should do insert when the 'where' condition > is false BUT > It's not doing this. It is executing the stored procedure for all kind > of condition whether it is true or false. > > Is there any syntax error in definition b'coz I was not able to find out > any example in > manual like this. > > Meanwhile my table definition is following > > create table "scorpion".bll_edit_map > ( > bll_mth_id integer not null disabled , > edit_id integer not null disabled , > prd_cd char(2) not null disabled , > eff_date date not null disabled , > dis_date date not null disabled , > emp_id integer not null disabled , > aty_cd char(1) not null disabled , > tna_dtime datetime year to fraction(5) not null disabled > ) extent size 16 next size 16 lock mode page; > > I will highly appreciate for any help in this. > > Thanks

Thanks ART & RUDY I was also hitting my head in wall and guess the same thing. I ended up in making both EXECUTE and INSERT conditional to WHEN clause (with in one pair of parenthesis, seprated by comma). It's working fine now. ART, Is it wrong b'coz you mentioned to use another WHEN clause testing the negation, please let me know. Somehow I was not able to see any example of this kind in manual, which book is best for detailed reference. Is Informix Press book on STORED PROCEDURES AND TRIGGERS is good or some other book. Again, I appreciate your efforts. Thanks "Art S. Kagel" wrote: > No. The unqualified, or nconditional, action (ie the insert) is executed > for every row. To make it conditional you need another WHEN clause testing > the negation of the condition in the first since there is no otherwise > clause. In fact a particular row CAN satisfy multiple conditions and so > trigger multiple WHEN action clauses. The WHEN is NOT a C switch or 4GL > CASE statement. It is more like sequential if {} blocks followed by an > unconditional {} block than an if{} followed by none or more else if{} blocks > followed by an else clause. > > Art S. Kagel > > Sanjeev Sagar wrote: > > > > I have one trigger defined like following > > > > create trigger "scorpion".tupd_bll_edit_map update of eff_date, > > dis_date,emp_id,aty_cd,tna_dtime on "scorpion".bll_edit_map > > referencing old as pre new as post > > for each row > > when (((select count(*) from "scorpion".bll_edit_map > > x0 where ((((x0.bll_mth_id = post.bll_mth_id ) AND (x0.edit_id = > > post.edit_id ) ) AND (x0.eff_date < post.dis_date ) ) AND > > (x0.dis_date > > > post.eff_date ) ) ) > 1. ) ) > > (execute procedure "scorpion".user_exception(338 )), > > ( > > insert into st3hist:"scorpion".bll_edit_map (bll_mth_id, > > edit_id,prd_cd,eff_date,dis_date,emp_id,aty_cd,tna_dtime) values > > (pre.bll_mth_id ,pre.edit_id ,pre.prd_cd ,pre.eff_date ,pre.dis_date> > > > ,pre.emp_id ,pre.aty_cd ,pre.tna_dtime )); > > > > As per my understanding, It should do insert when the 'where' condition > > is false BUT > > It's not doing this. It is executing the stored procedure for all kind > > of condition whether it is true or false. > > > > Is there any syntax error in definition b'coz I was not able to find out > > any example in > > manual like this. > > > > Meanwhile my table definition is following > > > > create table "scorpion".bll_edit_map > > ( > > bll_mth_id integer not null disabled , > > edit_id integer not null disabled , > > prd_cd char(2) not null disabled , > > eff_date date not null disabled , > > dis_date date not null disabled , > > emp_id integer not null disabled , > > aty_cd char(1) not null disabled , > > tna_dtime datetime year to fraction(5) not null disabled > > ) extent size 16 next size 16 lock mode page; > > > > I will highly appreciate for any help in this. > > > > Thanks

Sanjeev Sagar wrote: > Thanks ART & RUDY > > I was also hitting my head in wall and guess the same thing. I ended up in making > both EXECUTE and INSERT conditional to WHEN clause (with in one pair of > parenthesis, seprated by comma). It's working fine now. ART, Is it wrong b'coz > you mentioned to use another WHEN clause testing the negation, please let me > know. No, its not. Examine the syntax diagram for 'CREATE TRIGGER'. You will find that you are allowed multiple WHEN conditions as well as multiple actions within each WHEN. In fact, if you want to fire off multiple actions on a single WHEN, its sensible to place them within the single WHEN (to avoid duplication of code & maintenance problems later). Aside from the question of syntax, I was wondering whether the trigger definition in its original form was, in fact, correct. Looking at it from a business point of view (admittedly, without much of a background into the specifics), it does not seem illogical to maintain a history of every single change to a row, while triggering some exception if the change is outside some pre-defined parameters. Just a thought! > > > Somehow I was not able to see any example of this kind in manual, which book is > best for detailed reference. Is Informix Press book on STORED PROCEDURES AND > TRIGGERS is good or some other book. SQL Syntax seems quite clear to me. Happy holidays, Rudy

In article <38600815.DCABB3B5@hotmail.com>, Sanjeev Sagar <sanjeevsagar@hotmail.com> wrote: > > Thanks ART & RUDY > > I was also hitting my head in wall and guess the same thing. I ended up in making > both EXECUTE and INSERT conditional to WHEN clause (with in one pair of > parenthesis, seprated by comma). It's working fine now. ART, Is it wrong b'coz > you mentioned to use another WHEN clause testing the negation, please let me > know. > > Somehow I was not able to see any example of this kind in manual, which book is > best for detailed reference. Is Informix Press book on STORED PROCEDURES AND > TRIGGERS is good or some other book. > > Again, I appreciate your efforts. > > Thanks > I can recommend Informix Stored Procedure Programming by Michael L. Gonzales (Informix Press, ISBN 0-13-206723-4) (available from www.prenhall.com with an IIUG discount if applicable). I'd also recommend SPL Workstation (aka Server Studio in its latest guise) software (www.agsltd.com). We use this to monitor/maintain our SPL/triggers. I have no connection with AGS Limited, other than being a satisfied customer (Shock, horror!). Regards Glyn Balmer -- If it always works, why don't parachutists pull the emergency 'chute first? Sent via Deja.com http://www.deja.com/ Before you buy.