Trigger Help...

Hello All, I am using IDS 731.UC2 on solaris 2.5.1 I am facing one problem while doing update on one table. I need help in that. Following is my table and trigger definition { TABLE "scorpion".bll_edit_map row size = 34 number of columns = 8 index size = 0 } create table "scorpion".bll_edit_map ( bll_mth_id serial not null , 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 ) in sopd2st1d_curr_01 extent size 8 next size 8 lock mode page; revoke all on "scorpion".bll_edit_map from "public"; create trigger "scorpion".tdel_bll_edit_map delete on "scorpion" .bll_edit_map referencing old as pre for each row when (((pre.eff_date <= CURRENT year to fraction(3) ) AND ((TODAY - interval( 35) day(9) to day ) < pre.dis_date ) ) ) ( execute procedure "scorpion".user_exception(108 )), when (((TODAY - interval( 35) day(9) to day ) >= pre.dis_date ) ) ( insert into at1hist:"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 ), insert into at1hist:"scorpion".bll_edit_map (bll_mth_id, edit_id,prd_cd,eff_date,dis_date,emp_id,aty_cd,tna_dtime) selectpre.bll_mth_id ,pre.edit_id ,pre.prd_cd ,pre.eff_date ,pre.dis_date ,x0.emp_id ,'D' , CURRENT year to fraction(3) from "scorpion".emp_ct x0 where (x0.login_name = USER ) ); create trigger "scorpion".tins_bll_edit_map insert on "scorpion" .bll_edit_map referencing 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. ) ) ( -- before -- when (((select count(*) from "scorpion".subsys_mode x0 -- ,"scorpion".emp_ct x1 where (((x0.subsys_id = 1 ) AND (x0.emp_id = -- x1.emp_id ) ) AND (((x1.login_name != USER ) AND (x0.mode_id = 1 -- ) ) OR ((x1.login_name = USER ) AND (x0.mode_id IN (1 ,2 )) ) ) ) -- ) = 0. ) ) -- ( -- data entry not allowed in reprocessing mode -- except by the user who initiated reprocess -- execute procedure "scorpion".user_exception(500 )) execute procedure "scorpion".user_exception(217 )); 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. ) ) ( -- before -- when (((select count(*) from "scorpion".subsys_mode x0 -- ,"scorpion".emp_ct x1 where (((x0.subsys_id = 1 ) AND (x0.emp_id = -- x1.emp_id ) ) AND (((x1.login_name != USER ) AND (x0.mode_id = 1 -- ) ) OR ((x1.login_name = USER ) AND (x0.mode_id IN (1 ,2 )) ) ) ) -- ) = 0. ) ) -- ( -- data entry not allowed in reprocessing mode -- except by the user who initiated reprocess -- execute procedure "scorpion".user_exception(500 )) execute procedure "scorpion".user_exception(338 )), ( insert into at1hist:"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 )); create trigger "scorpion".tupd_bll_edit_map2 update of bll_mth_id, edit_id,prd_cd on "scorpion".bll_edit_map before ( execute procedure "scorpion".user_exception(339 )); My user_exception definition is following CREATE PROCEDURE "scorpion".user_exception(error_number INTEGER) DEFINE error_message VARCHAR(255); SELECT message INTO error_message FROM user_error WHERE error_num = error_number; IF error_message IS NULL THEN LET error_message="Undefined Scorpion Message."; END IF; RAISE EXCEPTION -746, 0, "("||error_number||") "||error_message; END PROCEDURE; Now I am trying to update dis_date (discontinue date) like > update bll_edit_map set dis_date='01/05/2000' where bll_mth_id=4 andedit_id=17; 746: (217) Undefined Scorpion Message. Error in line 1Near character position 78 I am not able to understand that why it's giving error. As per my understanding it should insert a pre image in history database. The weird nature is that it's giving error code which is mentioned in insert trigger. Any help would be appreciated. Before also I got help from ART & RUDY which helped me in improving my understanding of triggers. I appreciate that. Thanks

"Sanjeev K. sagar" wrote: > > Hello All, > > I am using IDS 731.UC2 on solaris 2.5.1 > > I am facing one problem while doing update on one table. I need help in > that. Following is my table and trigger definition > > { TABLE "scorpion".bll_edit_map row size = 34 number of columns = 8 > index size = > 0 } > create table "scorpion".bll_edit_map > ( > bll_mth_id serial not null , > 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 > ) in sopd2st1d_curr_01 extent size 8 next size 8 lock mode page; > revoke all on "scorpion".bll_edit_map from "public";> > create trigger "scorpion".tdel_bll_edit_map delete on "scorpion" > .bll_edit_map referencing old as pre > for each row > when (((pre.eff_date <= CURRENT year to fraction(3) > ) AND ((TODAY - interval( 35) day(9) to day ) < pre.dis_date > ) ) ) > ( > execute procedure "scorpion".user_exception(108 )), > > when (((TODAY - interval( 35) day(9) to day ) > >= pre.dis_date ) ) > ( > insert into at1hist:"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 ), > insert into at1hist:"scorpion".bll_edit_map (bll_mth_id, > edit_id,prd_cd,eff_date,dis_date,emp_id,aty_cd,tna_dtime) select> pre.bll_mth_id > ,pre.edit_id ,pre.prd_cd ,pre.eff_date ,pre.dis_date ,x0.emp_id ,'D' > , > CURRENT year to fraction(3) from "scorpion".emp_ct x0 where > (x0.login_name > > = USER ) ); > > create trigger "scorpion".tins_bll_edit_map insert on "scorpion" > .bll_edit_map referencing 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. ) ) > ( > -- before > -- when (((select count(*) from "scorpion".subsys_mode x0 > -- ,"scorpion".emp_ct x1 where (((x0.subsys_id = 1 ) AND > (x0.emp_id = > -- x1.emp_id ) ) AND (((x1.login_name != USER ) AND > (x0.mode_id = 1 > -- ) ) OR ((x1.login_name = USER ) AND (x0.mode_id IN (1 ,2 > )) ) ) ) > -- ) = 0. ) ) > -- ( > -- data entry not allowed in reprocessing mode > -- except by the user who initiated reprocess > -- execute procedure "scorpion".user_exception(500 )) > execute procedure "scorpion".user_exception(217 )); > > 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. ) ) > ( > -- before > -- when (((select count(*) from "scorpion".subsys_mode x0 > -- ,"scorpion".emp_ct x1 where (((x0.subsys_id = 1 ) AND > (x0.emp_id = > -- x1.emp_id ) ) AND (((x1.login_name != USER ) AND > (x0.mode_id = 1 > -- ) ) OR ((x1.login_name = USER ) AND (x0.mode_id IN (1 ,2 > )) ) ) ) > -- ) = 0. ) ) > -- ( > -- data entry not allowed in reprocessing mode > -- except by the user who initiated reprocess > -- execute procedure "scorpion".user_exception(500 )) > execute procedure "scorpion".user_exception(338 )), > > ( > insert into at1hist:"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 )); > > create trigger "scorpion".tupd_bll_edit_map2 update of bll_mth_id, > edit_id,prd_cd on "scorpion".bll_edit_map > before > ( > execute procedure "scorpion".user_exception(339 )); > > My user_exception definition is following > > CREATE PROCEDURE "scorpion".user_exception(error_number INTEGER) > DEFINE error_message VARCHAR(255); > > SELECT message INTO error_message FROM user_error > WHERE error_num = error_number; > > IF error_message IS NULL THEN > LET error_message="Undefined Scorpion Message."; > END IF; > > RAISE EXCEPTION -746, 0, "("||error_number||") "||error_message; > > END PROCEDURE; > > Now I am trying to update dis_date (discontinue date) like > > > update bll_edit_map set dis_date='01/05/2000' where bll_mth_id=4 and> edit_id=17; > > 746: (217) Undefined Scorpion Message. > Error in line 1> Near character position 78 > > I am not able to understand that why it's giving error. As per my > understanding it should insert a pre image in history database. > > The weird nature is that it's giving error code which is mentioned in > insert trigger. Is it possible that you cloned the alt_hist database from the scorpion database complete with triggers and the trigger THERE is generating the error? Art S. Kagel

I agree with Art's assessment - its the cloned trigger on the history table which is probably being fired. Besides, there seem to be a couple of anomalies in the trigger definitions. Given that bll_mth_id is type SERIAL (and, therefore, unique), the insert trigger will never fire as count(*) with an equality on bll_mth_id can never be > 1. The same applies to the user_exception part of the update trigger. Rudy "Sanjeev K. sagar" wrote: > Hello All, > > I am using IDS 731.UC2 on solaris 2.5.1 > > I am facing one problem while doing update on one table. I need help in > that. Following is my table and trigger definition ...

You people are AWESOME!!! Cloned Triggers were the problem. Now those triggers are working fine. Once again thanks to Art's and Rudy. Rudy Fernandes wrote: > I agree with Art's assessment - its the cloned trigger on the history table > which is probably being fired. > > Besides, there seem to be a couple of anomalies in the trigger definitions. > Given that bll_mth_id is type SERIAL (and, therefore, unique), the insert > trigger will never fire as count(*) with an equality on bll_mth_id can > never be > 1. The same applies to the user_exception part of the update > trigger. > > Rudy > > "Sanjeev K. sagar" wrote: > > > Hello All, > > > > I am using IDS 731.UC2 on solaris 2.5.1 > > > > I am facing one problem while doing update on one table. I need help in > > that. Following is my table and trigger definition > > ...