> Ouch. Had you posted here it would have been spotted.

The problem is that this is a rather large system. Once i made sure that
the problem is in THIS particular trigger, the answer kinda popped out,
after staring at the screen for thirty minutes or so, hehe.

The funny thing was that this particular code was the first part that i
scrutinized, but after scratching my head for some time, i couldn't see
how it could fail, so i moved on to 'widen the net'.

> We have created
> an external function that wraps a call to OutputDebugString which is
> invaluable in debugging PSQL. We use it to confirm the values in
> particular variables when debugging issues. I recommend you try
> something similar.

Interesting. Is there a built-in text logging feature planned for
Firebird? It might come in handy for something like this that needs full
tracing. Otherwise maybe i'll cook up some stored procedure that writes
to an EXTERNAL TABLE.... someone stop me before i'm barking up the wrong
tree again :).

> Please note I am not suggesting this is how it executes, I am saying
> that unless there is actually something returned, the variable will
> not be affected.

Yes. Ms Borrie's response also indicated that this is a 'programmer
error' :). I'm going to go kill myself now.

> select null
> from RDB$DATABASE
> into :lookup_var;
> What is in lookup_var.

If it's up to me,
I would argue that lookup_var should be NULL at this point :).
"I keed, I keed~~!! :)".

> NULL does not mean empty. It means that the value of a particular
> field is unknown or unspecified. Google for the Firebird NULL guide
> for all the gotchas there.

Thanks. Will do.

> There is a difference between 'not knowing the value where a record is
> known to exist' and 'knowing that no records exist'.

So maybe to prevent the same bug to ever see me face to face again,
would it be better to change the "check whether something exists"
construct from :
...
select (blah) from (blah_table) into :blah_var;
if (:blah_var is null) then begin
-- normal processing here
end;
...

to
...
if (exists(blah blah blah)) then begin
select (blah) from (blah_table) into :blah_var;
...
-- normal processing here
end
...

The downside is that it'd be slightly slower due to having to do the
query twice.

> You also correctly worked out you need to initialise it first. It can
> be a difficult error to find, especially if you are used to languages
> like Delphi which tend to pre-initialise variables for you.

+1 Clairvoyant. I'm a longtime Delphi guy :).

Thanks again.
t.s.