Excellent! Thank you!

John

--- In firebird-support@yahoogroups.com, "Ivan Prenosil"
<Ivan.Prenosil@s...> wrote:

> Use multicolumn PK/index, and assign CURRENT_TRANSACTION
> to the additional column.
>
> Ivan
>
>
> ----- Original Message -----
> From: "johnsparrowuk" <jsparrow@e...>
> To: <firebird-support@yahoogroups.com>
> Sent: Friday, March 26, 2004 2:33 PM
> Subject: [firebird-support] Stopping indexes see through

Transactions?

>
>
> > If I insert something into a PK field (say '1'), and then do the

same

> > in another transaction context (without committing the first).

It

> > will fail, yes?
> >
> > Presumably the same is true of other unique indexes or

constraints -

> > they can 'see through' the transaction context?
> >
> > Is there a way of preventing this from happening (maybe not with

PK's

> > but with indexes?)
> >
> > Sometimes I use a table as working storage for a sproc, and use
> > transacton isolation to prevent one instance seeing data from
> > another. When each instance is finnished they 'delete from

mytable'

> > so the committed state of the table is always empty. (I also

empty

> > the table at the start of the sproc, just to be sure!)
> >
> > Good idea? I can't use PK's because these see through the

context, so

> > if I must maintain uniqueness I usually have a trigger:
> >
> > before insert trigger:
> > select count(*) from mytable where pk = :new.pk into :a;
> > if (a > 0) then
> > exception key_viol;
> >
> > Which seems a bit inefficient...
> >
> > I suppose I could use a non-unique index to make the trigger
> > faster.. ;)
> >
> > Thanks,
> >
> > John