Sql-server – SQL Server 2008 R2 – How does the SQL Trigger actually work in this case

sql serversql-server-2008-r2trigger

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

enter image description here

I also have designed a trigger on that table

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

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

enter image description here


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.

Best Answer

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.