Re: [firebird-support] Trigger not working

[liviuslivius]

Hi,

do not use global temporary table to debug
it is “temporary” and its data can dissapear after transaction or connection end
use normal table with autonomous transaction and this will be ok

regards,
Karol Bieniaszewski

From: Werner 
Sent: Monday, February 24, 2014 9:49 AM
To: firebird-support@yahoogroups.com 
Subject: Re: Odp: [firebird-support] Trigger not working

  

Hi Karol and Dimitry,

Thanks for the tips.

On 23/02/2014 14:24, liviusliv...@poczta.onet.pl 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