Hi,
Did a bit of googling and found the trick of putting in an exception and
I also changed it to try to write to a temp table.
CREATE OR ALTER trigger bottag_biud0 for bottag
active before insert or update or delete position 0
as
declare variable oldid bigint;
declare variable newid bigint;
declare variable curcap integer;
declare variable oldcap integer;
declare variable newcap integer;
begin
curcap = 0;
oldcap = 0;
newcap = 0;
/* if it was assigned to rack */
if (old.fk_winerackit_id is not Null) then
begin
select id, usedcapacity from winerackit wi
where wi.id = old.fk_winerackit_id
into :oldid, :curcap;
oldcap = :curcap-1;
end
/* if it is newly assigned to rack */
if (new.fk_winerackit_id is not Null) then
begin
select id, usedcapacity from winerackit wi
where wi.id = new.fk_winerackit_id
into :newid, :curcap;
newcap = :curcap+1;
end
if (:oldid is not Null) then
update winerackit set usedcapacity=:oldcap
where winerackit.id = :oldid;
insert into triggertest (type, quantity) values('old', :oldcap);
if (:newid is not Null) then
update winerackit set usedcapacity=:newcap
where winerackit.id = :newid;
insert into triggertest (type, quantity) values('new', :newcap);
end
The exception I had after the insert into triggertest but I don't see
any entry in triggertest with or without the exception.
The excption shows me that the oldcap/newcap values are what I would
expect but they are never written to the table.
Anyone sees what stupid mistake I make?
Werner
