6

I have been looking for definitive documentation regarding the isolation level ( or concurrency or scope ... I'm not sure EXACTLY what to call it) of triggers in SQL Server.

I have found the following sources which indicate that what I believe is true (which is to say that two users, executing updates to the same table --even the same rows-- will then have independent and isolated triggers executed):

The first question is essentially the same question I am trying to find the answer to, but the answer given doesn't provide any sources. The second question also hits near the mark, and the answer is the same, but again, no sources are provided.

Can someone point me to where the available documentation makes the same assertions?

Thanks!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
reidLinden
  • 4,020
  • 4
  • 31
  • 46
  • Yes. 1 user will not be able to see the data from a second user at the same time. Well of course that assumes the trigger isn't using read uncommitted or nolock. This is simple to prove to yourself. Create a table with a trigger. Then open 1 tab in SSMS and begin a transaction and do an insert. Don't commit that transaction and do the same in a second tab. The second tab can't see the uncommitted data from tab 1. – Sean Lange Feb 17 '15 at 14:28
  • I'm not sure you'll find any official documentation that specifically states this in the form you want. But it's quite easy to imagine how chaotic trigger development would be if it were not so. – Damien_The_Unbeliever Feb 17 '15 at 14:36
  • @Damien_The_Unbeliever There are some official documents that I linked to that give bits and pieces of this. – Solomon Rutzky Feb 17 '15 at 19:28

1 Answers1

11

Well, Isolation Level and Scope are two very different things.

Isolation Level
Triggers operate within a transaction. By default, that transaction should be using the default isolation level of READ COMMITTED. However, if the calling process has specified a different isolation level, then that would override the default. As per usual: if desired, you should be able to override that within the trigger itself.

According to the MSDN page for DML Triggers:

The trigger and the statement that fires it are treated as a single transaction, which can be rolled back from within the trigger. If a severe error is detected (for example, insufficient disk space), the entire transaction automatically rolls back.

Scope
The context provided is:

{from you}

two users, executing updates to the same table --even the same rows

{from the first linked MSDN article in the Question that is "essentially the same question I am trying to find the answer to"}

Are the inserted and deleted tables scoped to the current session? In other words will they only contain the inserted and deleted records for the current scope, or will they contain the records for all current update operations against the same table? Can there even be truely concurrent operations or will locks prevent this?

Before getting into the inserted and deleted tables it should be made very clear that there will only ever be a single DML operation happening on a particular row at any given moment. Two or more requests might come in at the exact same nanosecond, but all requests will take their turn, one at a time (and yes, due to locking).

Now, regarding what is in the inserted and deleted tables: Yes, only the rows for that particular event will be (and even can be) in those two pseudo-tables. If you execute an UPDATE that will modify 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 the inserted and deleted Tables states:

The deleted table stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the deleted table. The deleted table and the trigger table ordinarily have no rows in common.

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. The rows in the inserted table are copies of the new rows in the trigger table.

Tying this back to the other part of the question, the part relating to the Transaction Isolation Level: The Transaction Isolation Level has absolutely no effect on the inserted and deleted tables as they pertain specifically to that event/query. However, the net effect of that operation, which is captured in those two psuedo-tables, can still be visible to other processes if they are using the READ UNCOMMITTED Isolation Level or the NOLOCK table hint.

And just to clarify something, the MSDN page linked above regarding the inserted and deleted tables states at the very beginning that they are "in memory" but that is not exactly correct. Starting in SQL Server 2005, those two pseudo-tables are actually based in tempdb. The MSDN page for the tempdb Database states:

The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server and is used to hold the following:

  • ...

  • Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

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:

  • operate within a Transaction
  • are static (i.e. read-only) tables
  • are visible to only the current Trigger
  • only contain rows for the specific event/operation/query that fired that instance of that Trigger
Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171