Hi,

Please accept my apologies for my shallow knowledge of SQL and
Firebird but I have problem that per my understanding of how things
are supposed to work make me believe there is a bug in the database
itself.

I am using Firebird-1.0.0.796-Win32 on Windows 2000 Professional.
Normally database access is made through the JDBC driver but the
problem occurs in ISQL also.

The problem is that a trigger that I have defined considers 'TFIX_18'
equal to 'TFIX_18 '. In this example I have used the single quote
characters to delineate where spaces are. The single quotes are not
part of my data, nor are the spaces. Or rather, a requirement of my
application is that there should be no spaces in the end-user's data.

Anyway, I have two tables named AIRSPACE_NODE and AIR_LINK. The key
field in the AIRSPACE_NODE table is ASN_ID. ASN_ID is a varying char
field of 35 characters which cannot be set to null.

In AIR_LINK I have two fields called ASN_INITIAL_ID and ASN_FINAL_ID
respectively. I shall only discuss the ASN_FINAL_ID field but the
problem exists for the ASN_INITIAL_ID field also.

As you may have guessed, ASN_INITIAL_ID and ASN_FINAL_ID are foreign
keys of ASN_ID in the AIRSPACE_NODE table.

I have purposefully avoided using the database's built in referential
integrity capabilities via usage of the REFERENCES clause. I did this
because the default error messages produced by the database were too
technical for usage by my end users.

Therefore I have implemented referential integrity via triggers
instead. Thus, I can define within the database more meaningful error
messages.

The specific trigger that I have defined is as follows:

CREATE EXCEPTION EXCEPT_296 'ASN_FINAL_ID not in AIRSPACE_NODE';
SET TERM !! ;
CREATE TRIGGER TRIG_156 FOR AIR_LINK
BEFORE UPDATE POSITION 156 AS
DECLARE VARIABLE CNT INTEGER ;
BEGIN
IF (NEW.ASN_FINAL_ID IS NOT NULL) THEN
SELECT COUNT(*) FROM AIRSPACE_NODE
WHERE ASN_ID=NEW.ASN_FINAL_ID INTO :CNT;
IF (CNT=0) THEN EXCEPTION EXCEPT_295 ;
END !!
SET TERM ; !!

Given the above trigger, could someone explain why the following
occurs in ISQL (and essentially the same behavior in my proprietary
database GUI editing system)?:

>>>>>>>>>>>>>> START ISQL SESSION

SQL> select ASN_ID from AIRSPACE_NODE;

ASN_ID
===================================

TFIX_36
TASN_CAB_36
TASN_CAB_18
TFIX_18

SQL> select ASN_FINAL_ID from AIR_LINK;

ASN_FINAL_ID
===================================

TFIX_36
TASN_CAB_18

SQL> update air_link set ASN_FINAL_ID='TFIX_36 ' where
ASN_FINAL_ID='TFIX_36';
SQL> update air_link set ASN_FINAL_ID='TFIX_36s' where
ASN_FINAL_ID='TFIX_36';
Statement failed, SQLCODE = -836

exception 296
-ASN_FINAL_ID not in AIRSPACE_NODE
SQL> update air_link set ASN_FINAL_ID='TFIX_36 i' where
ASN_FINAL_ID='TFIX_36';
Statement failed, SQLCODE = -836

exception 296
-ASN_FINAL_ID not in AIRSPACE_NODE

>>>>>>>>>>>>>> FINISH ISQL SESSION

As you can see, the first update statement was happily accepted and
executed. In my proprietary GUI I can determine that after executing
the equivalent update statement the extra spaces have been placed in
the ASN_FINAL_ID field.

But, 'TFIX_36 ' is not equal to 'TFIX_36' where I come from, am I
missing something?

Thank you very much for your time.