[Firebird-devel] Question on ON CONNECT / ON DISCONNECT trigger

[Thomas S. <ts...@ib...>]

Hello,

I've got a question on an ON CONNECT / DISCONNECT trigger, which is 
probably just a misunderstanding from my side.

I basically have a log table and a ON CONNECT / DISCONNECT trigger, 
which inserts a log record into this table.

The DDL:


CREATE TABLE LOG
(
   ID                         INTEGER         NOT NULL,
   DATETIME                 TIMESTAMP         DEFAULT CURRENT_TIMESTAMP,
   TRANSACTIONID              INTEGER         DEFAULT CURRENT_TRANSACTION,
   OPERATION                  VARCHAR(    20)  COLLATE NONE
);

ALTER TABLE LOG ADD CONSTRAINT PK_LOG PRIMARY KEY
   (ID);

COMMIT;

CREATE GENERATOR LOG_GEN;

COMMIT;


SET TERM  ^^ ;
CREATE TRIGGER LOG_ID FOR LOG ACTIVE BEFORE INSERT POSITION 0 AS
begin
   if ( (new.ID is null) or (new.ID = 0) )
   then new.ID = gen_id(LOG_GEN, 1);
end
  ^^
SET TERM ;  ^^

COMMIT;


set term !!;
CREATE OR ALTER TRIGGER TRI_LOG_ON_CONNECT
ACTIVE ON CONNECT POSITION 32767
AS
BEGIN
   insert into log (operation) values ('CONNECT');
END
!!
set term ;!!

set term !!;
CREATE OR ALTER TRIGGER TRI_LOG_ON_DISCONNECT
ACTIVE ON CONNECT POSITION 32767
AS
BEGIN
   insert into log (operation) values ('DISCONNECT');
END
!!
set term ;!!

commit;


Now, when connecting with isql in a dos shell via:

isql.exe localhost:db_trigger_test.fdb -user sysdba -password masterkey

I always get two entries in the log table:

SQL> select * from log order by id desc;

           ID                  DATETIME TRANSACTIONID OPERATION
============ ========================= ============= ====================
           70 2007-09-26 17:29:38.0930            214 CONNECT
           69 2007-09-26 17:29:38.0930            214 DISCONNECT


but I didn't disconnect, because I simply connected via isql. The other 
observation is that the logged transaction id is the same. So the 
DATETIME value.


Also, assume, I'm connected with several isql instances. When I close 
the dos shell I won't see the DISCONNECT operation instantly.


I'm a bit confused, especially regarding the DISCONNECT trigger. Should 
there be one CONNECT entry in the log table and one DISCONNECT entry 
when the application really disconnects?



Thanks,
Thomas