> > 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 ?

Digging further in MS SQL help ...

CHECK constraints appear useless in our case since they seem to be record
level only.

Let's see what triggers can do...

In the CREATE TRIGGER section, there is a sample clearly stating that since
CHECKs can't be used for multi-records or multi-tables constraints, triggers
can be used to implement such business rules.

The example, shows to ensure an inserted/updated employee sees its ability
level meet the range defined in the job table.

I broke this one as well...

--
Jerome