Data Integrity in an SQL database

I would like to know in as much detail as possible an effective means of ensuring data integrity between a parent record and its associated child rows. For example:

I have a SALESORDER table
I have a SALESORDERITEM table

I want to ensure that before I apply a save to one SALESORDER record, another user has not modified the same record. I can do this quite easily with optimitic locking:
UPDATE SALESORDER SET TS = @newTS WHERE PK = @PK and TS = @oldTS

I also want to ensure before I apply a save to the same SALESORDER record, the COUNT of all associated SALEORDERITEM rows is the same as it was when the SALESORDER was retrieved in the application.

I also want to ensure before I apply a save to the same SALESORDER record, the TS of each SALEORDERITEM record has not changed. (There may be pending updates to an item not yet committed
so simply SELECTING won't work as these changes would not be visible in the SALESORDER transaction)

And finally, I also want to ensure before I apply a save to any one SALEORDERITEM associated to the single SALESORDER, the SALESORDER has not changed.

My concerns are performance (as these are OLTP functions that must run quick). I don't want deadlocks occuring or keep them limited and the solution is guaranteed safe for data integrity given the above constraints...   Thanks!
Start Free Trial