Trigger/Stored Proc-Problem
Hi group,
I wrote a small Stored Procedure calculating some values for a
given costumer-number.
Then I'm doing an insert and, if a value for that specific
custumer number already exists (error -268) I'm doing an update
instead. This sounds quite easy and runs correctly (for both
possibilities (INSERT and UPDATE)), if I call the SP from
dbaccess using:
EXECUTE PROCEDURE fill_stat (36); (for example)
The second step I did was to write an update-trigger to call
this SP if a status-column of this customer changes to a
specific value. This trigger is even easier (although it was
th first trigger i wrote in my life). However, if I call that
trigger implicitely with
UPDATE KDRECHNUNG SET STATUS = 'FKT' WHERE KR_SYSNR = 36I get the following error:
-268 Unique Constraint pk_stat_partner violated.
I used the trace-statement in the SP; it looks like exception
handlers are not found in a SP when called from triggers.
Has anyone encountered simelar problems, or can anyone help
me to find my error?
Here are the codes for SP and trigger (no table definitions):
Stored Procedure:
create procedure "dbadm".fill_stat(var_kr_sysnr integer)
define var_gp_sysnr integer;
define var_update integer;
define var_gf_nr char(5);
define var_jahr datetime year to year;
define var_monat datetime month to month;
define var_fab char(1);
define var_warenart char(2);
define var_mwstkz char(1);
define var_stk_umsatz decimal(20,2);
define var_wert_umsatz decimal(20,2);
set debug file to 'fill_stat.trc';
trace on;
select extend(fakturierungsdatum,year to year),
extend(fakturierungsdatum, month to month)
into var_jahr, var_monat
from kdrechnung
where kr_sysnr = var_kr_sysnr;
foreach cur_rechnungspos for
select
m.gp_sysnr,m.gf_nr,d.fab,d.warenart,
d.mwstkz,sum(d.posbetrag), sum(d.liefermenge)
into
var_gp_sysnr,var_gf_nr,var_fab,var_warenart,
var_mwstkz,var_wert_umsatz,var_stk_umsatz
from kdrechnung m, kdrechnungspos d
where m.kr_sysnr = var_kr_sysnr
and d.kr_sysnr = m.kr_sysnr
group by m.gp_sysnr,m.gf_nr,d.fab,d.warenart,d.mwstkz
begin
on exception in (-268) -- Record already exists
update stat_partner
set stk_umsatz = stk_umsatz + var_stk_umsatz,
wert_umsatz = wert_umsatz + var_wert_umsatz
where gp_sysnr = var_gp_sysnr
and gf_nr = var_gf_nr
and jahr = var_jahr
and monat = var_monat
and fab = var_fab
and warenart = var_warenart
and mwstkz = var_mwstkz; -- Primary Key-Columns
end exception;
insert into stat_partner
values ( var_gp_sysnr,
var_gf_nr,
var_jahr,
var_monat,
var_fab,
var_warenart,
var_mwstkz,
var_wert_umsatz,
var_stk_umsatz); end;
end foreach
end procedure;
Trigger:
create trigger "dbadm".tu_kdrechnung
update of status on "dbadm".kdrechnung
referencing new as nacher
when ((nachher.status = 'FKT' ))
(execute procedure "dbadm".fill_stat(nachher.kr_sysnr ));
Many thanks in advance,
Peter