Hi,
I have the following trigger which doesn't work for me, i.e. the columns
in winerackit are never updated by it.
In my current test case the 'fk_winerackit_id' column is set to "Null",
so I would expect this trigger to fire and set the 'usedcapacity' column
to '0' as it was '1' before the update to the bottag table.
I must do something really stupid. Any hints on how I could debug this,
e.g. tutorial on how to write values to some log table would be very
appeciated.
Werner
CREATE OR ALTER trigger bottag_biud0 for bottag
active after 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;
if (:newid is not Null) then
update winerackit set usedcapacity=:newcap
where winerackit.id = :newid;
end
