Use multicolumn PK/index, and assign CURRENT_TRANSACTION
to the additional column.

Ivan

----- Original Message -----
From: "johnsparrowuk" <jsparrow@...>
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