✈️ Title:

Database Triggers in Airline Management System

Objective

To implement database triggers for handling:

• Ticket Booking (INSERT)

• Booking Cancellation (UPDATE)

• Booking Deletion (DELETE)

🗂️ Database Tables

1. Flights Table

CREATE TABLE flights (

   flight_id INT PRIMARY KEY,

   flight_name VARCHAR(50),

   total_seats INT,

   available_seats INT

);

2. Passengers Table

CREATE TABLE passengers (

   passenger_id INT PRIMARY KEY,

   name VARCHAR(50)

);

3. Bookings Table

CREATE TABLE bookings (

   booking_id INT PRIMARY KEY,

   flight_id INT,

   passenger_id INT,

   status VARCHAR(20)

);

4. Booking Log Table

CREATE TABLE booking_log (

   log_id INT AUTO_INCREMENT PRIMARY KEY,

   message VARCHAR(255),

   log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

⚙️ Triggers Implementation

🔹 1. BEFORE INSERT Trigger (Validation)

CREATE TRIGGER before_booking_insert

BEFORE INSERT ON bookings

FOR EACH ROW

BEGIN

  DECLARE seats INT;

  SELECT available_seats INTO seats

  FROM flights

  WHERE flight_id = NEW.flight_id;

  IF seats <= 0 THEN

     SIGNAL SQLSTATE '45000'

     SET MESSAGE_TEXT = 'No seats available';

  END IF;

END;

🔹 2. AFTER INSERT Trigger (Seat Reduction + Log)

CREATE TRIGGER after_booking_insert

AFTER INSERT ON bookings

FOR EACH ROW

BEGIN

  UPDATE flights

  SET available_seats = available_seats - 1

  WHERE flight_id = NEW.flight_id;

  INSERT INTO booking_log(message)

  VALUES (CONCAT('Booking confirmed for Passenger ID ', NEW.passenger_id));

END;

🔹 3. AFTER UPDATE Trigger (Cancellation Handling)

CREATE TRIGGER after_booking_update

AFTER UPDATE ON bookings

FOR EACH ROW

BEGIN

  IF NEW.status = 'Cancelled' AND OLD.status != 'Cancelled' THEN

     UPDATE flights

     SET available_seats = available_seats + 1

     WHERE flight_id = NEW.flight_id;

     INSERT INTO booking_log(message)

     VALUES (CONCAT('Booking status changed to Cancelled for Passenger ID ', NEW.passenger_id));

  END IF;

END;

🔹 4. AFTER DELETE Trigger (Seat Restore)

CREATE TRIGGER after_booking_delete

AFTER DELETE ON bookings

FOR EACH ROW

BEGIN

  IF OLD.status != 'Cancelled' THEN

     UPDATE flights

     SET available_seats = available_seats + 1

     WHERE flight_id = OLD.flight_id;

  END IF;

  INSERT INTO booking_log(message)

  VALUES (CONCAT('Booking deleted for Passenger ID ', OLD.passenger_id));

END;

🧪 Sample Data

INSERT INTO flights VALUES (1, 'Air India 101', 100, 100);

INSERT INTO passengers VALUES (1, 'Rahul'), (2, 'Priya');

📊 Execution & Output

✅ 1. INSERT Operation

INSERT INTO bookings VALUES (1, 1, 1, 'Confirmed');

Output:

• Seats reduced from 100 → 99

• Booking added

• Log created

✅ 2. UPDATE Operation

UPDATE bookings

SET status = 'Cancelled'

WHERE booking_id = 1;

Output:

• Seat restored → 100

• Status updated

• Log created

✅ 3. DELETE Operation

DELETE FROM bookings

WHERE booking_id = 1;

Output:

• Booking removed

• No duplicate seat increase (fixed logic)

• Log created

⚠️ Important Note

Without proper condition in DELETE trigger, seats may increase twice. This issue is handled using:

IF OLD.status != 'Cancelled'

🎯 Conclusion

• Triggers automate airline booking operations

• Maintain seat consistency

• Provide audit logs

• Reduce manual errors