This is normal behavior. You can't select <null> into any variable, indeed,
that variable holds it's previous value.
The workaround for me is to use coalesce(lookup_field,-1), or use any other
value that can't exists in the lookup table. That will show you taht it
doesn't exist.

SanTa

----- Original Message -----
From: "t.s." <truesaint@...>
To: <firebird-support@yahoogroups.com>
Sent: Monday, April 03, 2006 5:35 AM
Subject: [firebird-support] Firebird Trigger Local Variable Gotcha


> Dear All,
>
> Just thought i'm gonna share this little problem that a customer
> reported last friday. Took me 2 days to pinpoint the problem.
>
> In an AFTER UPDATE trigger for a MASTER table, i have the following code:
> ...
> -- for each detail record
> for select blah blah blah
> from detail_table
> where (master_id = new.master_id)
> do begin
>
> -- fetch lookup information
> select lookup_field
> from lookup_table where (lookup_id = new.lookup_id)
> into :lookup_var;
>
> -- only process if the lookup information exists
> if (lookup_var is null) then begin
> ...
> ...
> ...
> end
> end
> ...
>
> Can anyone spot the bug in this ? :)
>
> I was expecting the "lookup_var" local variable to be set to NULL if the
> lookup data does not exists. Somehow firebird (1.5.3, Win32) ignores the
> "select into" if the select returns NULL.
>
> Adding initialization code BEFORE the 'fetch' operation solves the
> problem, but this sure is a difficult 'bug' to find. Has anyone ran into
> this problem before? Is this normal, or did i run into a bug ?
>
> Thanks in advance,
> t.s.
>
>
>
>
>
>
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>
>