title: Weird Behavior of Trigger
body: <p>I've got a trigger (SQL 2008 R2) that does one simple operation but the results are not logical. 
  Here is the overview:</p>
  
  <p>A text file is fed to an SSIS package with ONE line(one record) that loads it into "ORDERS_IN_PROCESS" table. The Data Access Mode is set to "Table or view" in the "OLE DB Destination" to allow triggers to fire.</p>
  
  <p>Here is my ORDERS table:</p>
  
  <pre><code>OrderID        ItemNo
  ---------    ---------
  9813           1
  9813           2
  9813           3
  9817           1
  </code></pre>
  
  <p>So, SSIS executes and
  ORDERS_IN_PROCESS gets one record inserted which is OrderID 9813</p>
  
  <p>Trigger is fired:</p>
  
  <pre><code>INSERT INTO ORDERS_ARCHIVE SELECT * FROM ORDERS WHERE OrderID=INSERTED.OrderID
  </code></pre>
  
  <p>Pretty simple so far...</p>
  
  <p>The results I get in my ORDERS_ARCHIVE (identical layout to ORDERS) Table are</p>
  
  <pre><code>OrderId       ItemNo
  ---------   ----------
  9813          3
  </code></pre>
  
  <p>Where are the rest of the 2 line items?</p>
  
  <p>Note, it only inserted the last row read from ORDERS table into ORDERS_ARCHIVE.</p>
  
  <p>I need all 3 of them in ORDERS_ARCHIVE. </p>
  
  <p>Why does this happen?</p>
  
  <p>I believe it has something to do with the way SSIS processes it using "OLE DB Destination" because if I insert a record into RLFL manually, the trigger does exactly what it's supposed to do and inserts all 3 records from BACK.</p>
  
  <p>You may argue that trigger fires once per batch and I agree but in this case I have a batch of just ONE record.</p>
  
  <p>I'm thinking of an sp, but i'd rather not add another level of complexity for something so trivial, supposedly.</p>
  
  <p>Any ideas?</p>
  
  <p>Thanks.</p>