Ann,

> At 03:42 PM 6/4/2004, johnsparrowuk wrote:
>
> >...indexing a varchar (300) field. It's too big to index -
> > no problem. Write a 'before insert' trigger [that checks for

duplicates]

> >
> >...except that it won't work like an index because it can't see
> >uncommitted values... If I could dirty-read this wouldn't be a
> >problem. If dirty reading is 'always bad' then how come indexes
> >can do it?!?!? grin
>
> Because it's the Mommy, that's why.
>
> Seriously, the right answer is to allow longer index keys.

Still, wouldn't it be interesting to have a way to flag a trigger as running
in the system context like declarative constraints would ?
This would really help when you want integrity constraints that can't be
formulated with decl constraints or when you explicitly don't want to use
decl constraints (e.g you don't want to introduce a poorly selective index).
AFAIU, currently, writing custom constraints in triggers that reads data
outside the modified record isn't 100% safe since a trigger runs in the
client's transaction context (which could be in "snapshot" mode).

Regards.

--
Jerome