SQL Server 2008 R2 - How does the SQL Trigger actually work in this case?

Question

I have a table named _Test which has the following values stored in it.
Primary Key : Id

I also have designed a trigger on that table

ALTER Trigger [dbo].[_Test_Data_Save]
On [dbo].[_Test]
For Insert
As

--Some custom algorithm which takes about 2-3 seconds to complete

Now, 3 records were saved at the same time from 3 different sessions at the same microsecond like in the image below.

Question

Now my question is how will the trigger work for the last 3 records? Will the last 3 records fire 3 different triggers:-

1. simultaneously
2. one by one (according to the order they are saved in)
3. in any random order SQL seems fit

If the trigger is not executed one by one then what can i do so that they execute the trigger for the record of Id=5 first then after completion of that Id=6 and then trigger for Id=7.

Answer 1

Trigger code executes in the scope of the transaction that invokes it. Since, as you say, each of these three records is inserted by a separate session, this means three different transactions were completed, each inserting one record and executing trigger code for that one record.

If you initiate three (or more) transactions "simultaneously", you cannot control the order in which parts of execute transaction will execute beyond setting the transaction isolation level. If the isolation level is set to serializable, then the trigger code will execute "one by one (according to the order they are saved in)"; otherwise it will seem to run "in any random order SQL seems fit", subject to whatever locks each transaction obtains.

Answer 2

Warning: since I don't have enough reputation I can't write a comment and this is a comment.

AFAIK SQL server have only FOR EACH STATEMENT triggers. That means the trigger will modify simultaneously the rows.

A solution if you want to update each row is to use cursors which they are a performance killer. Perhaps more skilled people have better approach.