For this scenario :
update Table1 with (TABLOCK, XLOCK)
then a "INSTEAD OF TRIGGER" on Table1 update Table1 with other value
Does the trigger keep the initial (TABLOCK, XLOCK) or the lock is released and another one is made for the trigger?
The exclusive table lock will be held until the end of the execution of the trigger.
Triggers execute within the transactions that fire them, exclusive locks are held until the end of the transaction, hence that lock will be held for the duration of the trigger's execution.
btw, you could have just used the TABLOCKX hint, that does the same as those two combined.
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass