How to make a trigger that will compare input with a value of other table?
SQL Server Answer

CREATE TRIGGER FightNumberExists ON dbo.Bookings
AFTER INSERT
AS
IF EXISTS (SELECT fk_flight_number
           FROM inserted i
           INNER JOIN Flights f 
              ON f.flight_number = i.fk_flight_number
           WHERE i.date_of_reservation &lt; f.date_of_departure   
      )
BEGIN
    RAISERROR ('Flight Number does not exist', 16, 1);
    ROLLBACK TRANSACTION;

    RETURN 
END;

GO


Notes
In SQL Server the values are first loaded into the 'inserted' or 'deleted' table before being written to the physical table.

You can accomplish this with adding constraints to the tables as well.