At 01:35 PM 3/04/2006, you wrote:

>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.

Sorry, no coconut. :-) A local variable is *just* a variable. So,
like any variable, it keeps its last value until something actually changes.

Where your concept goes wrong is your assumption that, if a select
returns no row, you would get something like "a row of
nulls". Nope. There is no such thing as "an empty row" in SQL. So,
if you are fetching into variables in a loop you *must* initialise
them, just as you do in any programming language.

>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 ?

No, you made a programming error. ;-)

./heLen