Select with(nolock) hint in after trigger | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Select with(nolock) hint in after trigger

Quick question. Using the hint with(nolock) inside an after trigger is pointless, right ? Because the whole thing is inside a transaction, so every select sometable I do inside a trigger will lock the row/page/extent/whatever that I’m selecting. Right ?

Firstly, its easy to verify this by using sql profiler, with the "lock aquired/released" and "Mode" data columns selcted. (i would filter by the object id too). Secondly,IMHO the (nolock) in the trigger might be on something else than the resource that has the trigger…so its not meaningless M