Re: Bug calling a stored procedure from a trigger ???

Manuel I have done something like what you seem to be attempting to do. In my case I wanted to insert a row into a table, but if it was already there I wanted to update it. The following SP is called from the Update, Insert and Delete triggers and works from all. Create Procedure "gladmin".upd_prior_att_cnts(Origin Char(3), Current_Date Date, Attendance Int, Party_Attendance Int, Mid_B Char) Define SQLCODE Int; Define Next_Year_Date Date; Define Fiscal_YYYY Integer; Define Fiscal_Q Integer; Define Fiscal_MM Integer; Define Fiscal_WW Integer; Define Fiscal_DD Integer; ON Exception Set SQLCODE END Exception with Resume; Let SQLCODE = 0; Select Fiscal_Year, Fiscal_Week, Fiscal_Day Into Fiscal_YYYY, Fiscal_WW, Fiscal_DD From Fiscal_Day Where Calendar_Date = Current_Date; If SQLCODE != 0 THEN RAISE Exception -746, 0, 'Date NOT Found in Fiscal_Day'; END IF Let Fiscal_YYYY = Fiscal_YYYY + 1; Let SQLCODE = 0; Select Calendar_Date, Fiscal_Quarter[2,2], Fiscal_Month Into Next_Year_Date, Fiscal_Q, Fiscal_MM From Fiscal_Day Where Fiscal_Year = Fiscal_YYYY and Fiscal_Week = Fiscal_WW and Fiscal_Day = Fiscal_DD; If SQLCODE != 0 THEN RAISE Exception -746, 0, 'Next Year Date NOT Found in Fiscal_Day'; END IF Let SQLCODE = 0; Insert Into Attendance_Tbl (Origin_Cde, Date, Fiscal_Year, Fiscal_Quarter, Fiscal_Month, Fiscal_Week, Fiscal_Day, Pr_Year_Attn, Pr_Year_Party_Attn, Mid_B) Values (Origin, Next_Year_Date, Fiscal_YYYY, Fiscal_Q, Fiscal_MM, Fiscal_WW, Fiscal_DD, Attendance, Party_Attendance, Mid_B); If SQLCODE != 0 THEN Let SQLCODE = 0; Update Attendance_Tbl Set Pr_Year_Attn = Attendance, Pr_Year_Party_Attn = Party_Attendance Where Origin_Cde = Origin and Date = Next_Year_Date; If SQLCODE != 0 THEN RAISE Exception -746, 0, 'Unable to Update the Attendance_Tbl'; END IF END IF END Procedure; Hope this helps Michael J. Erickson >I have a field in a table that each time it changes I must write a record >in a log table. >For that purpose I wrote a stored procedure... > > create procedure ins_hist (v_not like t_reclamos.cod_notificacion, > v_est like t_reclamos.estatus_rec)> > -- as you can see, sometimes I'm trying to insert a duplicate > on exception in (-268) > trace "se fue por el on exception... dup val"; > return; > end exception > > -- inserta en el historial de estatus de reclamos > insert into t_his_est_rec > (cod_notificacion, cod_estado, fecha_hora, usuario) > values > (v_not, v_est, current, user);> trace "ok"; > > end procedure; > > >...that is called from a couple of triggers of the table... > > create trigger ins_reclamos insert on t_reclamos referencing > for each row > (execute procedure ins_hist(rec.cod_notificacion, > rec.estatus_rec));> > create trigger upd_reclamos update of estatus_rec on t_reclamos > referencing new as rec > for each row > (execute procedure ins_hist(rec.cod_notificacion, > rec.estatus_rec));> > >Sometimes I'm trying to insert a duplicate value but I don't want >my program to fail for that reason, I just want it to continue, >not even showing an error, so I programmed an exception that >will trap this error in the stored procedure (as you can see in >the code). > >If I force the error just calling the stored procedure, it works >just fine: the first call inserts a record and the second doesn't >and it doesn't show an error > > set debug file to "/tmp/mads"; > execute procedure ins_hist ("C1", "E"); > execute procedure ins_hist ("C1", "E");> >The problem is that if I force the error by using the triggers >(inserting or updating the table) IT DOESN'T WORK, the second >update fails and shows the -268 error: > > set debug file to "/tmp/mads"; > > update t_reclamos > set estatus_rec = "E" > where cod_notificacion = "C1"; > > update t_reclamos > set estatus_rec = "E" > where cod_notificacion = "C1"; > >I've this problem on 2 different machines: > > HP-UX B.10.01 9000/887 with INFORMIX-OnLine Version 7.13.UC3 >and > HP-UX B.10.20 9000/831 with INFORMIX-OnLine Version 7.20.UC2 > >The shortcut that I'm using is to "select count" before the insert >and then decide if I insert depending on this, but I'm aware that's >NOT the efficient way to do this. > >Any ideas? > >-- >Manuel A. Daponte Santiago (mdaponte@prtc.net) >MSC No. 54, Montehiedra Town Center, >9410 Los Romeros Ave. >San Juan, P.R. 00926 >+1 (787) 7514343 ext. 512 >