Subject Re: [IBO] Problem posting a record when an uppercase shadow field is present.
Author Helen Borrie
At 04:54 PM 15-10-02 +0930, you wrote:
>The table structure is like:
>ID Integer
>Vendor Char(30) NOT NULL
>S_Vendor Char(30) NOT NULL UNIQUE
>with an AfterUpdate trigger that updates "S Vendor" as UPPER(Vendor).
>ISC ERROR: validation error column S_Vendor, value NULL.
>How do I correct this assuming that S_Vendor must be unique but Vendor
>need not be unique?

If you want to do this in the client app then it's an IBO solution. If you
want to do it using database rules, then it's a database question. The
clue to the second solution is that you don't use After Update triggers to
set column values, but Before Update triggers - the reason being that
validation occurs before the update, not after it.

Here's a sample trigger (off topic):

create trigger bi_mytable for mytable
active before insert position 0
new.S_Vendor = upper(new.Vendor);

However, your validation problem isn't coming from the fact that S_Vendor
is unique, it's coming from the fact that you passed NULL to a non-nullable
column. You will have to solve the problem of non-uniqueness in VENDOR by
applying the unique constraint to Vendor (in which case you won't need it
for S_Vendor).

On the client side (if you want to) you can perform the uppercasing in your
BeforePost event. It's not usually what you'd want to do though. In IBO
you would set the NOT_REQUIRED ColumnAttribute in order to allow the BI
trigger to do its work without the NULL field getting tripped up on the
client side as a required field.