Does SQL database trigger security threads?

Question

I was interested to learn about the semantics of concurrency regarding SQL Server database triggers. I have a database trigger that runs after updating a table. The trigger sets the column "dateModified" at the current time. This allows me to always know that the last table update occurred X times. Below it will be shown how it looks:

ALTER TRIGGER base.TR_myTrigger ON base.myTable AFTER INSERT, UPDATE AS BEGIN DECLARE @dateModified AS DATETIMEOFFSET SET @dateModified = SYSDATETIMEOFFSET() UPDATE base.myTable Set dateModified = @dateModified WHERE id in (SELECT DISTINCT(id) FROM inserted) END 

In my scenario, the table can be updated at any time by any number of threads. Is this trigger safe in a multi-threaded context? If thread A updates the table, and thread B reads it right away, will B see the state of the table with updates A, but also not start updates? Or do triggers protect the table from reading until all actions + their triggers are completed?

Any understanding of what exactly is happening under the hood with SQL database triggers will be appreciated. Thanks!

Answer 1

If thread A updates the table, and thread B reads it right away, will B see the state of the table with updates A, but not start updates too? Or do triggers protect the table from reading until all actions + their triggers are completed?

What does it boil down to: is there a basic operation and a trigger operation, regarded as an atomic unit, or are they separated? Answer: atomic. That is, you will never see the results of an insert or update (in your case) and you will not see a dateModified column that can be contacted using a trigger. That is, they both make transactions. From the documentation :

The trigger and the operator that runs it are considered as one transaction ...

Answer 2

Triggers have no special properties regarding concurrency. They work as if you had executed this code manually.

Your trigger is safe, because all the lines that you read and write have already been locked by X using the starting DML.