or
sepupic imported from SE
sql-server
Here is as small repro:

    create table dbo.t (id int primary key, v int);
    insert into dbo.t values (1, 1), (2, 2);
    
    create table dbo.s (id int primary key, v int);
    insert into dbo.s values (1, 10);
    go
    
    create trigger dbo.tr_t__iou
    on dbo.t
    instead of update
    as
    begin
     set nocount on;
    
     exec sp_lock @@spid;
    end;
    go
    
    update dbo.t set v = 10 where id = 1;
    
    update t
     set
      v = 10
    from
     dbo.s s join
     dbo.t t on t.id = s.id;
    
    update t
     set
      v = 10
    from
     (values (1, 10)) s(id, v) join
     dbo.t t on t.id = s.id;
    go
    
    drop table dbo.t, dbo.s;
    go

`sp_lock` within the `trigger` reports U-`key lock` on the affected row in the first and the last cases, but in the second case there is no `lock` at all, how can it be explained?
Top Answer
Paul White
When the update statement qualifies for a trivial plan, the optimizer rule that expands the instead-of trigger part of the statement (`ExpandInsteadOfTriggerUpd`) includes the part of the plan that reads from the base table. This rewrite includes adding an `UPDLOCK` hint to the base read. As usual, the `UPDLOCK` hint means that update locks are taken and held to the end of the transaction.

When the statement does not qualify for a trivial plan, the `ExpandInsteadOfTriggerUpd` rule only [rewrites the write-cursor portion][1] of the plan, leaving the base table reads untouched - no `UPDLOCK` hint is added.

My guess is that this trivial plan behaviour exists to avoid a deadlock scenario.


  [1]: https://sqlperformance.com/2015/09/sql-plan/instead-of-triggers
Sometimes the updated rows are not locked within instead of update trigger

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.