SQL database triggers thread safe?
If thread A updates the table, and thread B reads from it immediately after, will B see a state of the table with A's updates but not trigger's updates too? Or do triggers protect the table from being read from until all actions + their triggers have been performed?
What this boils down to is: are the underlying operation and the trigger operation treated as an atomic unit or are they separate? The answer is: atomic. That is, you'll never see the results of the insert or update (in your case) and not see the dateModified column being dealt with via the trigger. That is, they both commit in one transaction. From the documentation:
The trigger and the statement that fires it are treated as a single transaction...
Triggers do not have any special properties regarding concurrency. They run as if you had manually executed that code.
Your trigger is safe because all rows that you read and write have been X-locked already by the triggering DML.