SQL server 2008触发器在多次插入时不能正常工作

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.

我希望触发器所做的是，当添加一个新的checkin_flight记录时，检查tables Flight、checkin_destination和checkin_company。每个checkin_flight记录都包含一个航班号和desknumber，乘客需要在该目的地登记。该表checkin_destination和checkin_company包含有关公司和目的地的信息，这些信息仅限于某些checkin desk。当向checkin_flight添加一个记录时，我需要从航班表中获取信息，以获得目的地和带有插入的flightnumber的flightcompany。此信息需要与航班、目的地和公司的可用签入组合进行检查。

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

编辑1:我正在使用下面的多重插入语句

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.

编辑2:测试一个不可能的行插入，然后错误被正确抛出。这就是多重插入的问题。

4 个解决方案

#1

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.

将您的“如果不存在(…)”改为“如果存在(…)”并更改您的SELECT语句以返回无效的航班。

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

#2

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.

您的问题是如果不存在，如果插入的3行中有1行的条件为真，则该条件不存在。您需要转换它来发现问题行，如果存在，则使用错误。

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.

我有点怀疑缺少回报是你的问题，但当你在触发器中出错时，最好包含三个r。

#3

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

#4

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.

插入的表可以包含多个行，因此触发器中的所有逻辑必须能够应用到所有行。想法触发器必须每一行触发一次，这是一个常见的误解WRT触发器。SQL Server通常会将调用合并到触发器中，以提高在相同事务中发生的性能。

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.

要修复这个问题，您可以从插入的COUNT()开始，并将其与匹配条件的COUNT()进行比较，并在出现不匹配时抛出错误。

#1