1

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

enter image description here

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.

2
  • 1
    "3 records were saved at the same time". Please explain: were the rows inserted a. with a single statement? b. with 3 statements in a single transaction? c. with 3 statements in separate transactions? – ypercubeᵀᴹ Oct 13 '18 at 12:33
  • @ypercubeᵀᴹ it means 3 different sessions saved 1 record each at the same exact time. – Agent_Spock Oct 13 '18 at 12:44
2

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.

1
  • "Serializable" seems to be a nice option but there is a problem that is if the transaction was terminated in the middle of execution because of power failure or etc (i have tested by terminating the transaction in middle of debugging when the lock was made) then it keeps the lock forever until you delete the lock yourself. Instead of that i thought of using TABLOCX on table because it automatically releases the table lock even if the transaction was interrupted in the middle. Please correct me if i am wrong. Still +1 for letting me know where to lock from. – Agent_Spock Oct 16 '18 at 6:45
0

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.

1
  • thanks for the reply. What is AFAIK SQL server? you mean to say each SQL statement will run an individual trigger which will run simultaneously to other triggers at that moment? – Agent_Spock Oct 13 '18 at 10:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.