How to create a trigger that will insert values into another table twice?

Question

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!

Answer 1

So the following trigger works perfectly to prevent double bookings for the case stated in the question:

CREATE OR REPLACE TRIGGER Check_Double_Booking
BEFORE INSERT OR UPDATE OF Aircraft_ID, Departure_Date, Arrival_Date ON 

Booking
FOR EACH ROW
BEGIN
INSERT INTO Flight VALUES (:new.Aircraft_ID, :new.Departure_Date);
INSERT INTO Flight VALUES (:new.Aircraft_ID, :new.Arrival_Date);
END;