John,

> > Do you think the problem lies in multi-versioning ?
>
> I think if you guys can't make this work properly then it's a
> weakness, yes.
>
> > Tell me how would you ensure the field value is unique without using
> > unique index/constraint in M$ ?
>
> In a trigger:
>
> if exists(select t.pk from inserted i inner join MyTable t on
> i.uniqueval = t.uniqueval and i.pk <> t.pk)
> begin
> raiserror('Dup value(s)',16,1)
> rollback transaction
> return
> end
>
> If you run this in a Read Committed transaction (default behaviour)
> it would stop and freeze when it came to an uncommitted record. It
> would either timeout (if you set the system up that way), or when
> rollback / commit occured in the other transaction it would proceed
> (with or without the conflicting value).

That's what I thought too. I was wrong ! I just verified this on my SQL 2000
server.
Once I commited tx #1, tx#2 committed as well nicely breaking your trigger !

Interesting. No ?

--
Jerome