Creating Stored Procs and Triggers

Flex Tron
Flex Tron used Ask the Experts™
on
Dear Gurus,
Needed your help.
I am working on an assignment in Oracle 11g
I have a table RESERVATION as shown below

RESERVATION
------------------------
RESERVATION_ID
SEATS_RESERVED
CUSTOMER_ID
FLIGHT_ID


The assignment is that :
Now Using a stored procedure and trigger(s), The system should be able to make a reservation.  This includes adding a valid customer (and the number of seats needed) to a valid flight provided that the maximum number of passengers for the flight (the plane) has will not be exceeded.  When a reservation is made, the total number of passengers on the flight must be updated. A reservation cannot be made on a flight in the past.

Can you please share the code which can be used here.

Thankyou.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
johnsoneSenior Oracle DBA

Commented:
Looks like homework to me...  What have you tried?

Seems like there is more than one table involved (at least some sort of customer table and flight table is missing).  And, I'm not sure you really need any kind of trigger.
Top Expert 2016

Commented:
you need a customer table, a flight table, reservations table.customer_orders
Each flight will have a seats and a reserved seats table
validate the customer from the customer table.
select a flight number from flight
if flights.available_seats - reserved_seats >= reservation_amount
flights_reserved_seats += reservation_amount
create a new entry in customer_orders
add new id, customer_id, flight_id, seats
Flex TronDeveloper

Author

Commented:
Dear Gurus,
This is what exactly the description of the assignment is as well as the question related to it. Needed your help.Sharing the ERD and sample code below
TASK
Airline Reservation System:  This database is for an airline reservation system.  You will need to track the flights available.   Each flight has a flight number, a destination city, a departure city, a date, a departure time (1-24), the total number of passengers booked on the flight. Each flight is assigned a specific plane.  A plane consists of an ID, a type (747, 777, 727), and a maximum occupancy.  Customers make reservations on flights.  For each customer, we want to track a last name, address, and frequent flyer program type (gold, silver, copper).  When a customer makes a reservation on a flight, the total number of seats reserved and a reservation ID is added to the system.  A customer may make many reservations and a reservation may be for more than one person.  

Questions 1.
(Using a stored procedure and trigger(s)) The system should be able to make a reservation.  This includes adding a valid customer (and the number of seats needed) to a valid flight provided that the maximum number of passengers for the flight (the plane) has will not be exceeded.  When a reservation is made, the total number of passengers on the flight must be updated. A reservation cannot be made on a flight in the past.
Questions 2
The system should be able to remove a reservation, remove a customer from a flight.  When a reservation is removed, the total number of passengers on the flight must be updated.  

Answers:
1.
create or replace PROCEDURE insertRESERVATION(
	   p_customer_id IN reservation.CUSTOMER_ID%TYPE,
	   p_flight_id IN reservation.FLIGHT_ID%TYPE,
	   p_reservation_num IN reservation.RESERVATION_NUM%TYPE
     )
IS
BEGIN

  INSERT INTO reservation (customer_id, flight_id, reservation_num,seats_reserverd) 
  VALUES (p_customer_id, p_flight_id,p_reservation_num,ins_passengers);

  COMMIT;

END;

/*Trigger Creation*/
CREATE OR REPLACE TRIGGER ins_passenegers BEFORE INSERT OR UPDATE OF TOTAL_PASSENEGERS ON FLIGHT
    FOR EACH ROW 
    DECLARE S_RESERVED NUMBER;
    
    BEGIN
    IF  :AIRPLANE.MAX_OCCUPANCY - FLIGHT.SEATS_RESERVERD >= 1
AND :FLIGHT.FLIGHT_DATE_TIME = SYSDATE
THEN
SET @S_RESERVED = @S_RESERVED + NEW.SEATS_RESERVED;
END IF;
END;

Open in new window


Issue: I am not sure if I am doing it correctly. This statement is showing in red in SQL developer too : SET @S_RESERVED = @S_RESERVED + NEW.SEATS_RESERVED;
Help highly needed. Thankyou.!

Answer to Question 2. Not yet started
ERD.xlsx
Senior Oracle DBA
Commented:
Since you have a specific question about the trigger, lets focus on that.
CREATE OR REPLACE TRIGGER ins_passenegers BEFORE INSERT OR UPDATE OF TOTAL_PASSENEGERS ON FLIGHT
    FOR EACH ROW 
    DECLARE S_RESERVED NUMBER;
    
    BEGIN
    IF  :AIRPLANE.MAX_OCCUPANCY - FLIGHT.SEATS_RESERVERD >= 1
AND :FLIGHT.FLIGHT_DATE_TIME = SYSDATE
THEN
SET @S_RESERVED = @S_RESERVED + NEW.SEATS_RESERVED;
END IF;
END;

Open in new window

Putting a : in front of something makes it a bind variable.  Putting a : in front of a table name does nothing, in this case it will generate an error.  Let's take a look at this line:

    IF  :AIRPLANE.MAX_OCCUPANCY - FLIGHT.SEATS_RESERVERD >= 1 AND :FLIGHT.FLIGHT_DATE_TIME = SYSDATE

You cannot reference a value from another table simply by putting the table name in there.  To get a value from the AIRPLANE table, you need to go out and select it.  The trigger has no idea what that is.

You should never reference the table that the trigger is triggered from in the trigger (this is called a mutating table error).  The trigger already has the information to the current row being processed (because of the FOR EACH ROW clause and the default REFERENCING NEW AS NEW OLD AS OLD clause).  Where you reference FLIGHT or :FLIGHT, that should likely be :NEW.

Now take a look at this line:

SET @S_RESERVED = @S_RESERVED + NEW.SEATS_RESERVED;

There is no SET statement in a trigger.  The use of @ is a SQL Server construct, not Oracle.  To set a value in the record being updated, just set the value that you can reference with the NEW bind variable.  To set a value in another table, you need to issue an update statement.

Doing things this way does not allow for concurrency.  Hopefully, you are looking at a single user system.

I also do not believe that you need to use a trigger for what you are trying to use it for.  You really should be putting that code in the reservation procedure.  The use of a trigger would come into play more with question 2, if you are going to allow a direct delete.

Looking quickly at the procedure, you probably shouldn't issue a COMMIT.  It should be up to the calling application to issue the COMMIT when all the work is completed successfully.  There could be other parts of the process that if they fail, the insert needs to be rolled back, but you have taken that capability away.

I'm certainly not going to do this for you, but if you have questions, I'll try to help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial