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!

• markp-fuso: what have you tried so far? have you tried 2x insert/select (or insert/values) queries?
• domin thomas: 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;
• domin thomas: @ I've created the trigger ^^^
• Anthony Genovese: You can answer your own question in a new answer. Then you can get double points!
• domin thomas: @AnthonyGenovese will give that a go!

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;