Triggers - Understanding firing

Transact-SQL
1

I am writing a trigger that processes data as it comes in from an external process. I cannot control how much data is sent for processing, or how often the data arrives.

I am curious to know what would happen in the following scenario:

  1. A first batch of data arrives, the trigger is fired and processes data. The data contains many records, and processing takes let's say 5 seconds for argument's sake.
  2. Three seconds later, another second batch of data arrives that should also fire the trigger (3 seconds into the first batch being processed which will still run for a further 2 seconds).

My question is, do the two instances of the trigger run at the same time (the second instance runs as soon as it is fired), or does the second instance fire, but wait for processing of the first to complete?

Thanks in advance.

1 Like
2

If your trigger is modifying same data and you're using transactions, then yes, the second batch of data will wait, until first batch of data has finished.

http://www.sqlteam.com/article/introduction-to-transactions

2 Likes
3

do the two instances of the trigger run at the same time (the second instance runs as soon as it is fired)

Typically yes, esp. for INSERTs, since INSERTs would normally have no reason to overlap each other and thus wouldn't block each other.

or does the second instance fire, but wait for processing of the first to complete?
No, it would not normally have to wait for another INSERT or UPDATE on the same table, unless they tried to modify the same row or otherwise blocked each other for similar reasons.