sqlsql-servertriggersatomictransaction-isolation

SQL Server: Atomic Trigger

I have two quick questions.

  1. Are SqlServer Triggers atomic by default?
  2. How to achieve it if not?

The environment is simple. I have two users (let's call them UserA and UserB) with two different connections to the database. Both connections are active at the same time.

When UserA inserts a row in a specific table, a trigger is fired (let's call it TriggerAtomic). I need to ensure that when the TriggerAtomic is fired, UserB can't execute anything until TriggerAtomic has finished. Obviously no other triggers, functions, procedures... can be executed until the TriggerAtomic finishes.

Solution

  • Put a transaction around the INSERT statement, and commit it afterwards. SQL Triggers operate in the transaction of the statement that triggered them.