Retrieve from DB value stored by INSERT trigger immediately after it executes

Question

In an SQLServer DB, a table exists (A) with an INSERT trigger on it. In that trigger, when a record is inserted into (A) a value is inserted/updated in another table (B).

A program using an ADO Recordset executes an INSERT into (A) by using AddNew/Update ADO Recordset methods. How safe is to try and read the value stored in table (B) immediately after the Recordset.Update is called on table (A)? Taking into consideration that this whole process is enclosed within a Transaction, is there any chance the "SELECT FROM B" statement is executed before the server has a chance to execute the "INSERT INTO B" statement from the trigger (for instance, on high-load of the server)?

Answer 1

Triggers execute synchronously, as part of the statement that caused them to fire. There is no risk that a following statement will execute before the trigger has completed its actions, unless the trigger is doing something to deliberately decouple its actions from the original statement - such as by using service broker to queue some form of change.

Assuming your trigger is not using service broker but is just performing a direct INSERT into table B, you're entirely safe.