StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL server 2008 trigger not working correct with multiple inserts
text
Body
copied!<p>I've got the following trigger;</p> <pre><code>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 </code></pre> <p>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.</p> <p>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?</p> <p>EDIT 1: I'm using the following multiple insert statement</p> <pre><code>INSERT INTO checkin_flight VALUES (5315,3),(5316,3),(5316,2) //5315 is the flightnumber, 3 is the desknumber to checkin for that flight </code></pre> <p>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.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload