Add SET TRANSACTION ISOLATION LEVEL READ COMMITTED LOCK
In SQL Server, the READ COMMITTED isolation level can be implemented in two ways: locking or snapshot. Currently, this is defined by a database setting. RCSI is wonderful for concurrency as it largely reduces blocking.
However, there is a risk with all snapshot isolation implmentations: you may be reading stale data. This is mainly a problem with validation. Consider a business rule that an active order may not include a discontinued product. Typically, you would implement this rules with triggers. Now, consider the case that someone places an order for product B, at the same time as a user marks this product as discontinued. The trigger for OrderDetails fires, and reads from the snapshot that B is still active. At the same time, the trigger for Products checks Orders and OrderDetails for active orders and reading the snapshot, it finds nothing. The final result is a violation of a business rule.
SQL Server does offer a solution to this: you can use the hint READCOMMITTEDLOCK to prevent this from happening.
However, adding this to every query in a long trigger is not palatable, and most users would only do in cases where it is really critical that the business rule is upheld (and they know about the hint).
It would be a lot easier, if READCOMMITTEDLOCK was promoted to an isolation level on its own, so that you could add a SET command on the top of your trigger.
Note: there are two older Connect items of mine asking for the same thing, but as voting is no longer enabled for these items, I'm adding a new one. If you wish to close one as a duplicate, please close the old ones and leave this one open.