Flights(FlightNo, DeptDateTime, Origin, ArrivalDateTime, Destination, Capacity, SoldSeats) Tickets(FlightNo, Seat, CustID)
Where
- flightNo is the PK of the flights table
- DeptDateTime holds the date and time of the flights departure
- Origin holds the airport code (e.g. RKE) of the flights origin
- ArrivalDateTime holds the date and time of the flights arrival
- Destination holds the airport code (e.g. ATL) of the flights destination
- Capacity is the number of passenger seats the flight can hold
- SoldSeats is the number of seats that are currently sold
- FlightNo+Seat is the PK of the Tickets Table
- FlightNo in the Tickets Table is an FK referencing the Flights Table
- Seat is the seat assigned to that ticket (e.g. 7B)
- CustID is the ID number of the customer that is assigned to that seat
Write a trigger or triggers that:
- increments the SoldSeats field when a ticket is inserted
- raises an exeception when an attempt to insert a ticket occurs
if the flight is already sold out
- decrements the SoldSeats field when a ticket is deleted
To provide appropriate transaction control your trigger should lock the associated flight row in the flights table before attempting to update the SoldSeats field.
Add sample data and test your triggers.
Have your triggers finished with some sample data entered by the start of class on November 16th.