I'm working on moving validation code for a reservation application from the reservation form to the DBC as row validation.

I need to make sure that whatever the changes are to the record being validated, the maximum number of seats available is not exceeded. Currently I have a Trips.Booked field where I keep track of how many seats are booked. If I can lock the Trips record and there are enough seats for the reservation, everything is cool. However, this client is growing and I may have to scale up to SQL Server. So record locking is no longer an option. Not to mention maintaining the validation code is a pain.

In my new scheme, I have the following tables (reduced for clarity)

Trips:
cTripNo
nCapacity

Reservation:
cResNo
cTripNo
nSeats
cStatusCode

ResStatus:
cStatusCode
cVerboseDesc
lCountAsBooked

Depending on ResStatus.lCountAsBooked, I need to make sure that Trips.nCapacity is not exceeded.

What I can't figure out is how to get the sum of reservation.nSeats for all records including the one I'm trying to add/change.

I've tried using a paramaterized view, but it does not include the value of the record I'm trying to validate, unless I'm editing an existing record, in which case it uses the record's original value.

Is my best option to use my local view as the sum of all existing records, subtract the original value if I am editing a record, and then add in the new value to see if I exceed the value of Trips.nCapacity?

If so, what is the danger of my parameterized view becoming outdated by another user saving changes at the same momement and how might I get around that?

TIA