Hi Karol and Dimitry,
Thanks for your tips, with them I managed to fix it.
Werner
Following just for the archive in case it might help someone else.
Not 100% but I think my initial problem had to do with not using
"begin/end" in my "if" blocks.
What did I change:
- trigger is now only for update and delete
- check that old/new.fk_winerackit_id are distinct and only then do the rest
- use begin/end in all the if-then blocks - I think not having them is
what caused my problem
CREATE OR ALTER trigger bottag_biud0 for bottag
active before 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;
/* only do update if they are not the same */
if (old.fk_winerackit_id is distinct from new.fk_winerackit_id) then
begin
/* 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 or re-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
begin
update winerackit set usedcapacity=:oldcap
where winerackit.id = :oldid;
end
if (:newid is not Null) then
begin
update winerackit set usedcapacity=:newcap
where winerackit.id = :newid;
end
end
end
