How to make a trigger that will compare input with a value of other table? I had made two tables with the following fields: 

TABLE 1: "Bookings"
  FIELD: "fk_flight_number"
  FIELD: "date_of_reservation"    

TABLE 2: "Flights"
  FIELD: "flight_number"
  FIELD: "date_of_departure"


I want to add a condition, just for the consistency of my data. I want to check ON INSERT:

1) in Bookings - fk_flight_number must exists in Flights

2) date_of_reservation must be smaller (&lt;) from date_of_departure.

How to do that? I have read about something similar and the keyword was TRIGGER. I think in my case it must be the same. Can you show me how to refer the second table? The logical conditions are not important - I can handle it. I need some snippet, because I don't know how to start writing the TRIGGER I need for. Or If you know some article describing similar problem - I will appreciate it too. Thank a lot. Of cource I'm not 100% sure that the best approach is TRIGGER. Any other advice will be appreciated too. One more time - thank you!
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.