Every 100th record?

Any suggestions about how to implement capturing every 100th order placed in real time?

My client wants every 100th order to qualify for a reward and the placer to be informed in real time.

As multiple customers could be placing orders over the Internet at the same time, with some transactions as yet uncommitted, I am looking for a good way to make sure that one and only one order is recognized as the 100th.

I also do not want some serial locking logic that could put transactions into a wait loop that causes TCP connections to time out and orders to be rolled back.

I could probably tolerate some flexibility over whether it was exactly 100th. i.e. if it was the 99th. or the 102nd. it might be OK. But ending with 99th. AND 102nd. is definitely NOT OK.

Thanks,
Clive.

I'd say a trigger that after insert checks if the record is 100th and sets some kind of flag, it won't see uncommitted data, but let me say that if a transaction is complete it's also committed and thus you shouldn't have any problem.
Or, couldn't your customer be satisfied with a nightly procedure that goes through the records, finds out the right ones and sends an email aknowledging the prize?

I wish!
My customer (probably correctly ) thinks that the instant gratification is important.

I was thinking along similar lines or a one record table with an integer field that is incremented and then checked for ( = 100); but I am not clear on how to both:
a. Lock the record (or table) while doing this.
b. Make sure other transactions wait for the lock to clear (it would only be locked momentarily) rather than return an exception to the caller.

My only real issue is to make sure not more than one order thinks it is the 100th.

Clive.