SQL server 2008 trigger not working correct with multiple inserts

Question

I've got the following trigger;

CREATE TRIGGER trFLightAndDestination
ON checkin_flight
AFTER INSERT,UPDATE
AS
BEGIN
    IF NOT EXISTS
    (
                    SELECT 1 
            FROM Flight v
            INNER JOIN Inserted AS i ON i.flightnumber = v.flightnumber
            INNER JOIN checkin_destination AS ib ON ib.airport = v.airport
            INNER JOIN checkin_company AS im ON im.company = v.company
            WHERE i.desk = ib.desk AND i.desk = im.desk
    )
    BEGIN
        RAISERROR('This combination of of flight and check-in desk is not possible',16,1)
        ROLLBACK TRAN       
    END
END

What i want the trigger to do is to check the tables Flight, checkin_destination and checkin_company when a new record for checkin_flight is added. Every record of checkin_flight contains a flightnumber and desknumber where passengers need to check in for this destination. The tables checkin_destination and checkin_company contain information about companies and destinations restricted to certain checkin desks. When adding a record to checkin_flight i need information from the flight table to get the destination and flightcompany with the inserted flightnumber. This information needs to be checked against the available checkin combinations for flights, destinations and companies.

I'm using the trigger as stated above, but when i try to insert a wrong combination the trigger allows it. What am i missing here?

EDIT 1: I'm using the following multiple insert statement

INSERT INTO checkin_flight VALUES (5315,3),(5316,3),(5316,2)
//5315 is the flightnumber, 3 is the desknumber to checkin for that flight

EDIT 2: Tested a single row insert which isn't possible, then the error is being thrown correct. So it's the multiple insert which seems to give the problem.

Answer 1

I'm not sure of your business logic, but you need to check that the query does the proper thing.

Your problem is the IF NOT EXISTS, if the condition is true for 1 of the 3 rows in INSERTED it does not exist. You need to convert it to find a problems row and use IF EXISTS then error out.

However, when in a trigger the best way to error out is:

RAISERROR()
ROLLBACK TRANSACTION
RETURN

I kind of doubt that the lack of a RETURN is your problem, but it is always best to include the three Rs when erroring out in a trigger.

Answer 2

The problem is that your logic is allowing any insert that includes at least one valid set of values through. It will only fail if all of the inserted records are invalid, instead of if any of the inserted records are invalid.

Change your "IF NOT EXISTS(...)" to a statement "IF EXISTS(...)" and change your SELECT statement to return invalid flights.

eg:

IF EXISTS
(
                SELECT 1 
        FROM Flight v
        INNER JOIN Inserted AS i ON i.flightnumber = v.flightnumber
        LEFT JOIN checkin_destination AS ib ON ib.airport = v.airport
             AND i.desk = ib.desk
        LEFT JOIN checkin_company AS im ON im.company = v.company
             AND i.desk = im.desk
        WHERE (im.desk IS NULL OR ib.desk IS NULL)
)
BEGIN
    RAISERROR('This combination of of flight and check-in desk is not possible',16,1)
    ROLLBACK TRAN       
END

Answer 3

The inserted table can contain multiple rows and therefore all logic within a trigger MUST be able to apply to all rows. The idea triggers must fire once per row effect is a common misunderstanding WRT triggers. SQL Server will tend to coalesce calls to a trigger to increase performance when they occur within the same transaction.

To fix you might start with a COUNT() of inserted and compare that with a COUNT() of the matching conditions and raise an error if there is a mismatch.

Answer 4

The problem is that the condition will be true if only one of the inserted records are correct. You have to check that all records are correct, e.g.:

if (
  (
    select count(*) from inserted
  ) = (
    select count(*) from flight v
    inner join inserted i ...
  )
) ...