sql server 2008 multiple inserts over 2 tables

Question

I got the following trigger on my sql server 2008 database

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

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?

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')

UPDATE: The table looks as below

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
)

Answer 1

If you run your trigger after inserting a record, and then look for a record with the values you just inserted, you will always find it. You might try an INSTEAD OF trigger so you can check for an existing records before actually doing the insert.

Answer 2

It might be throwing the error by finding itself in the table (circular reference back to itself). You might want to add an additional filter to the where clause like " AND Passenger.ID <> inserted.ID "

Answer 3

There are a few problems with this subquery:

SELECT 1
FROM Passenger p 
INNER JOIN Inserted i on i.flight= p.flight
WHERE p.flight= i.flight AND p.seat= i.seat

First of all, the WHERE p.flight = i.flight is quite unnecessary, as it's already part of your join.

Second, the p.seat = i.seat should also be part of the JOIN.

Third, this trigger runs after the rows have been inserted, so this will always match, and your trigger will therefore always raise an error and roll back.

You can fix the trigger, but a much better method would be to not use a trigger at all. If I understand what you're trying to do correctly, all you need is a UNIQUE constraint on flight, seat:

ALTER TABLE passgier
ADD CONSTRAINT IX_passagier_flightseat
UNIQUE (flight, seat)