I'm trying to create a trigger that will insert two values into another table. From table booking to table flight;
Here is my code for the booking table:
CREATE TABLE Booking ( Booking_ID NUMBER(10) NOT NULL PRIMARY KEY, Company_ID NUMBER(10) NOT NULL REFERENCES Penrhyn_Jet_Charter(Company_ID), Customer_ID NUMBER(10) NOT NULL REFERENCES Customer(Customer_ID), Aircraft_ID NUMBER(10) NOT NULL REFERENCES Aircraft(Aircraft_ID), Assignment_No NUMBER(10) NOT NULL, Booking_Date DATE DEFAULT SYSDATE, Charter_Cost NUMBER(14,2) CHECK(Charter_Cost > 0), Departure_Date DATE NOT NULL, Departure_Location CHAR(3) NOT NULL, Arrival_Date DATE NOT NULL, Arrival_Destination CHAR(3) NOT NULL );
Here is my code for table flight:
CREATE TABLE Flight ( Aircraft_ID NUMBER(10), Flight_Date date, constraint pk_flight primary key (aircraft_id, flight_date) );
The point of the trigger is to check for double bookings, so the Aircraft_ID and Departure_Date will be taken to the flight table as one entry.
Then the Aircraft_ID and Arrival_Date will be taken to the flight table as another entry.
if any of the entries were to be the same, then it would result in an error as in table flight, Aircraft_ID and Flight_Date are composite primary keys, at least that's the idea anyway.
So I'm stuck here trying to create a trigger:
CREATE OR REPLACE TRIGGER Check_Double_Booking BEFORE INSERT OR UPDATE ON Booking FOR EACH ROW BEGIN
Any ideas? Thank you!