CREATE PROCEDURE usp_PurchaseTicket(@CustomerID INT, @FlightID INT, @TicketPrice DECIMAL(8, 2), @Class VARCHAR(6), @Seat VARCHAR(5))
AS
    BEGIN
        BEGIN TRAN
        DECLARE @MaxTicketID INT = ISNULL((SELECT MAX(TicketID) FROM Tickets),0)+1;
        DECLARE @Balance  DECIMAL(8, 2) = (
                                            SELECT c.Balance
                                              FROM CustomerBankAccounts AS c
                                             WHERE c.CustomerID=@CustomerID
                                            )
        INSERT INTO Tickets VALUES(@MaxTicketID, @TicketPrice, @Class, @Seat, @CustomerID, @FlightID)
        UPDATE CustomerBankAccounts
           SET Balance = Balance-@TicketPrice
         WHERE CustomerID=@CustomerID

            IF (@Balance< @TicketPrice OR @Balance IS NULL)
               BEGIN
                    RAISERROR('Insufficient bank account balance for ticket purchase.', 16,1)
                    ROLLBACK
               END
                ELSE
                BEGIN
                    COMMIT
                END
END