Subject Re: [ib-support] Re: Helen: Low Selectivity Problem
Author Ann W. Harrison
At 12:06 PM 8/28/2002 -0500, Woody wrote:
> >
> > > >Triggers for integrity ... ouch.

There is a significant difference between triggers and
constraints. Trigger operate in the transaction context
and constraints operate in system context. If that's
not instantly clear to you, consider a unique constraint
contrasted with a trigger that checks for duplicates on
a particular column.

Transaction1 stores a record with a key value of 123
into the table.

Concurrently, transaction2 stores a record with a key
value of 123 into the table.

Each transaction would cause the before (or after) trigger
to fire. Neither trigger detects the presence of the
conflicting value because it was created by a concurrent
transaction. Triggers will not catch the duplicate except
in a "read committed no record_version" transaction*.

Unique constraints - and unique indexes - operate in a
system context. When they are evaluated, the system checks
both committed and uncommitted records and in the case of
uncommitted changes waits until the conflicting change is

Using triggers for referential integrity can run into the
same problem -


We have answers.

* Readers with long memories will know what I think of read committed
no record_version transactions in general...