DELIMITER //
CREATE TRIGGER after_reservation_insert
    AFTER INSERT ON reservations
    FOR EACH ROW
    BEGIN
DECLARE train_capacity INT;
DECLARE current_reserved_seats INT;
SELECT total_seats, COUNT(*) INTO train_capacity, current_reserved_seats
FROM trains
    INNER JOIN reservation ON trains.trains _id=reservations.trains_id
GROUP BY trains.train_id;

UPDATE trains
SET total seats =train_capacity -current_reserved_seats
WHERE train_id = NEW.train_id;
    END;
 //
DELIMITER;
 INSERT INTO reservations (train_id, passenger_id, reservation_date)
VALUES (1, 2, '2023-11-23 10:30:00');