Updating of new row is not allowed in after trigger
A transaction running under SNAPSHOT isolation level can view changes made by that transaction.
For example, if the transaction performs an UPDATE on a table and then issues a SELECT statement against the same table, the modified data will be included in the result set.
This prevents other transactions from modifying any rows that have been read by the current transaction.
Other transactions can insert new rows that match the search conditions of statements issued by the current transaction.
There must be no other open connection in the database until ALTER DATABASE is complete.
Except when a database is being recovered, SNAPSHOT transactions do not request locks when reading data.
The lock is released immediately after it has been granted.
The ALLOW_SNAPSHOT_ISOLATION database option must be set to ON before you can start a transaction that uses the SNAPSHOT isolation level.
Shared locks acquired for READ COMMITTED or REPEATABLE READ are generally row locks, although the row locks can be escalated to page or table locks if a significant number of the rows in a page or table are referenced by the read.
If a row is modified by the transaction after it has been read, the transaction acquires an exclusive lock to protect that row, and the exclusive lock is retained until the transaction completes.