Hello Helen,

> If you have certain char or varchar columns that need a case-insensitive
> search frequently, create an additional column of the same length which is
> populated automatically by before insert and before update triggers and
> stores your value as upper case. Index this proxy search column instead of
> the actual column.

[ snip ]

> create trigger bu_proxy_last_name for customer
> active before update position 0
> as
> begin
> if (new.last_name <> old.last_name
> and new.last_name is not null) then
> new.proxy_last_name = upper(new.last_name);
> end

I think you ought to extend the body to:

begin
if (new.last_name is null) then
new.proxy_last_name = null;
else if (old.last_name is null or new.last_name <> old.last_name) then
new.proxy_last_name = upper(new.last_name);
end

You have to test new.last_name against null because otherwise you
might leave old non-null values in the proxy column.

Second, if you test (new.last_name <> old.last_name) this will return
false if old.last_name is null. If you subsequently conclude that
new.last_name == old.last_name, you might leave the proxy column null
when you oughtn't. Hence the explicit test for old.last_name nullity.

The following expressions all return false:

null = null
null <> null
null = 'Pim'
null <> 'Pim'

Nasty buggers, nulls!


Greetings,
Paul Vinkenoog