Hi Karol and Dimitry,
Thanks for the tips.
On 23/02/2014 14:24, [email protected] wrote:
Hi,
To debbug trigger use log table in autonomous transaction context
avaiable in fb2.5 or use external table which is outside of
transaction management
I tried this one first but still nothing in my log table.
BTW, I created the log table like this:
CREATE GLOBAL TEMPORARY TABLE TRIGGERTEST (
"TYPE" VARCHAR(100),
QUANTITY VARCHAR(30)
) ON COMMIT PRESERVE ROWS;
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;
in autonomous transaction
do
begin
insert into triggertest (type, quantity) values('old',
:oldcap);
end
if (:newid is not Null) then
update winerackit set usedcapacity=:newcap
where winerackit.id = :newid;
in autonomous transaction
do
begin
insert into triggertest (type, quantity) values('new',
:newcap);
end
end
But I must still be missing something as nothing is written to
"triggertest".
I know that trigger runs and it has the correct values for my current
test case. I proved that with an exception just after the 'insert into
triggertest (type, quantity) values('old', :oldcap);" and the value for
oldcap and oldid are the correct one.
Werner
