sql server - Weird Behavior of Trigger -

sql server - Weird Behavior of Trigger -

i've got trigger (sql 2008 r2) 1 simple operation results not logical. here overview:

a text file fed ssis bundle 1 line(one record) loads "orders_in_process" table. info access mode set "table or view" in "ole db destination" allow triggers fire.

here orders table:

orderid itemno --------- --------- 9813 1 9813 2 9813 3 9817 1

so, ssis executes , orders_in_process gets 1 record inserted orderid 9813

trigger fired:

insert orders_archive select * orders orderid=inserted.orderid

pretty simple far...

the results in orders_archive (identical layout orders) table are

orderid itemno --------- ---------- 9813 3

where rest of 2 line items?

note, inserted lastly row read orders table orders_archive.

i need 3 of them in orders_archive.

why happen?

i believe has way ssis processes using "ole db destination" because if insert record rlfl manually, trigger it's supposed , inserts 3 records back.

you may argue trigger fires 1 time per batch , agree in case have batch of 1 record.

i'm thinking of sp, i'd rather not add together level of complexity trivial, supposedly.

any ideas?

thanks.

i don't think has ssis @ all, trigger. instead of in have there, seek using join in query:

insert orders_archive select o.* orders o inner bring together inserted on o.orderid = i.orderid

sql-server triggers ssis