How can I do this dynamical update in Oracle?

I have the model table defined like:

MODEL (id NUMBER, capacity NUMBER ) 

and the flight table defined like:

FLIGHT (flight_number NUMBER, available_seats NUMBER)

I want to initialize the available_seats field in FLIGHT with the capacity number defined in MODEL each time a new row is inserted in FLIGHT table. 开发者_如何学PythonAlso I want to notify the user when the available_seats counts down to zero. How can I do this?

---

I would write a stored procedure called REGISTER FLIGHT, which would accept as parameters the PK of the MODEL table (and any other parameters needed. The have the procedure do the lookup on the MODEL table, and insert the initial rows into the flight table.

I would then have a second procedure called BOOK_SEAT, which would in the process of booking the flight, decrease the number of available seats in the flight. If the booking decreases the available_seats to zero (or fewer), it would throw an exception (e.g. OVERBOOKED_EXCEPTION) and expect the caller to catch and handle the problem.