title: sql server 2008 multiple inserts over 2 tables
body: <p>I got the following trigger on my sql server 2008 database</p>
  
  <pre><code>CREATE TRIGGER tr_check_stoelen
  ON Passenger
  AFTER INSERT, UPDATE
  AS
  BEGIN
          IF EXISTS(
                  SELECT 1
                  FROM Passenger p 
                  INNER JOIN Inserted i on i.flight= p.flight
                  WHERE p.flight= i.flightAND p.seat= i.seat
               )
          BEGIN
              RAISERROR('Seat taken!',16,1)
              ROLLBACK TRAN   
          END
  END
  </code></pre>
  
  <p>The trigger is throwing errors when i try to run the query below. This query i supposed to insert two different passengers in a database on two different flights. I'm sure both seats aren't taken, but i can't figure out why the trigger is giving me the error. Does it have to do something with correlation?</p>
  
  <pre><code>INSERT INTO passagier VALUES 
  (13392,5315,3,'Janssen Z','2A','October 30, 2006 10:43','M'),
  (13333,5316,2,'Janssen Q','2A','October 30, 2006 11:51','V')
  </code></pre>
  
  <p>UPDATE: 
  The table looks as below</p>
  
  <pre><code>CREATE TABLE Passagier
  (
      passengernumber int NOT NULL CONSTRAINT PK_passagier PRIMARY KEY(passagiernummer),
      flight int NOT NULL CONSTRAINT FK_passagier_vlucht REFERENCES vlucht(vluchtnummer) 
          ON UPDATE NO ACTION ON DELETE NO ACTION,
      desk int NULL CONSTRAINT FK_passagier_balie REFERENCES balie(balienummer) 
          ON UPDATE NO ACTION ON DELETE NO ACTION,
      name varchar(255) NOT NULL,
      seat char(3) NULL,
      checkInTime datetime NULL,
      gender char(1) NULL
  )
  </code></pre>