Well, isolation level and scope are two different things.
Isolation level
Triggers work as part of a transaction. By default, this transaction should use the default isolation level of READ COMMITTED . However, if the calling process specified a different isolation level, this will override the default value. As usual: if you wish, you can override this in the trigger itself.
According to the MSDN page for DML Triggers :
The trigger and the operator that launches it are considered as one transaction that can be dropped from the trigger. If a serious error is detected (for example, insufficient disk space), the entire transaction is automatically rolled back.
Region
Provided context:
{from you}
two users performing updates in the same table - even the same rows
{from the first related MSDN article in the Question, which is "essentially the same question I'm trying to find the answer to"}
Are inserted and deleted tables included in the current session? In other words, will they contain only inserted and deleted records for the current area, or will they contain records for all current update operations on the same table? Could even true concurrent operations or locks prevent this?
Before getting into the inserted and deleted tables, it should be clearly stated that at any moment there will be only one DML operation in a certain row. Two or more requests may appear on the same nanosecond, but all requests will take place in turn, one at a time (and yes, due to blocking).
Now, regarding the inserted and deleted tables: Yes, only rows for this particular event will (and may even be) in these two pseudo-tables. If you execute UPDATE, which changes 5 rows, only those 5 rows will be in the inserted and deleted tables. And since you are looking for documentation, the MSDN page for Use inserted and deleted tables :
The remote table stores copies of the affected rows during DELETE and UPDATE. During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the remote table. The remote table and the trigger table usually do not have rows.
The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added to both the inserted table and the trigger table. Rows in the inserted table are copies of new rows in the trigger table.
Linking this to another part of the question, part related to the transaction isolation level: the transaction isolation level has absolutely no effect on the inserted and deleted tables, since they relate specifically to this event / request. However, the net effect of this operation, which is recorded in these two psuedo tables can still be visible to other processes if they use the READ UNCOMMITTED isolation level or the NOLOCK table hint.
And just to clarify something, the MSDN page linked above regarding inserted and deleted tables at the very beginning claims to be “in memory,” but that's not entirely correct. Starting with SQL Server 2005, these two pseudo-tables are actually based on tempdb . The MSDN page for tempdb Database says:
The tempdb system database is a global resource available to all users connected to an instance of SQL Server and is used to store the following data:
Prior to SQL Server 2005, the inserted and deleted tables were read from the transaction log (I believe).
To summarize, the inserted and deleted tables:
- work in a transaction
- are static (i.e. read-only) tables
- displayed only for the current trigger
- contain only lines for a specific event / operation / request that triggered this instance of this trigger