AFTER INSERT触发器是否会在UPDATE操作时触发？附死锁日志

AFTER INSERT触发器不会在普通UPDATE操作执行时触发。
只有对目标表执行INSERT操作、且数据行成功写入表后，AFTER INSERT触发器才会被激活。你捕获的执行栈里，INSERT语句下层出现的UPDATE操作，就是本次INSERT激活了对应AFTER INSERT触发器后，触发器内部调用WMSData.dbo.UpdateDes存储过程执行的逻辑，属于整个INSERT事务的一部分，不是UPDATE操作触发了INSERT触发器。

从死锁日志可以直接定位冲突双方和锁等待逻辑：

• 冲突双方操作的都是WMSData.dbo.Inventory表的PK_Inventory主键索引，两个事务都持有的是排他锁（X锁），申请的是更新锁（U锁），属于典型的不同事务遍历同一张表的索引顺序不一致导致的循环等待死锁。
• 事务1（spid=94，被选为死锁牺牲品）：执行dbo.postreceipt存储过程，先向Inventory表插入入库数据，插入完成后激活AFTER INSERT触发器，触发器执行全表更新逻辑：update inventory set designation = 'FROZEN' where designation <> 'FROZEN'，该语句走主键索引逐行扫描，申请U锁后升级X锁更新符合条件的行。
• 事务2（spid=59）：执行dbo.Postshipment存储过程，执行出库删除逻辑：delete from inventory where cases <= ...，同样走主键索引逐行扫描，申请U锁后升级X锁删除符合条件的行。
• 死锁形成过程：
  1. 事务2先扫描到键值为(c73677424644)的行，拿到该行X锁后继续向后扫描处理其他行
  2. 事务1完成插入操作，拿到自己刚插入的键值为(771c8a402149)的行的X锁，接着触发器的UPDATE语句扫描到事务2持锁的(c73677424644)行，申请U锁被阻塞
  3. 事务2继续扫描，刚好走到事务1刚插入的(771c8a402149)行，申请U锁被阻塞
  4. 两个事务互相持有对方需要的锁，同时等待对方释放，形成循环等待，触发数据库死锁检测机制，回滚开销更小的事务1作为牺牲品。

• 优先修复触发器逻辑：禁止在AFTER INSERT触发器中执行无范围限定的全表更新，通过触发器自带的inserted系统表关联主键，仅更新本次新插入的、符合designation <> 'FROZEN'条件的行，将锁范围从全表缩小到本次插入的少量行，从根源上消除大面积锁冲突。
• 核对Inventory表的索引设计，给designation、cases等筛选条件字段建立匹配的索引，避免UPDATE、DELETE语句走全主键索引扫描，减少持锁行数和遍历时间。
• 拆分长事务，将大批量的更新、删除操作拆分为小批次提交，缩短单事务持锁时间，降低锁冲突概率。

<deadlock>
 <victim-list>
  <victimProcess id="process1fb169468" />
 </victim-list>
 <process-list>
  <process id="process1fb169468" taskpriority="0" logused="2044" waitresource="KEY: 5:72057598307401728 (c73677424644)" waittime="61674" ownerId="34455794" transactionname="user_transaction" lasttranstarted="2022-07-11T23:24:01.387" XDES="0x56bee18e0" lockMode="U" schedulerid="6" kpid="9800" status="suspended" spid="94" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-07-11T23:24:01.387" lastbatchcompleted="2022-07-11T23:24:01.387" lastattention="2022-07-11T22:02:36.350" clientapp="Vizion WMS" hostname="AGROXAWORK02" hostpid="10484" loginname="vizion" isolationlevel="read committed (2)" xactid="34455794" currentdb="5" currentdbname="WMSData" lockTimeout="4294967295" clientoption1="671219744" clientoption2="128056">
   <executionStack>
    <frame procname="WMSData.dbo.UpdateDes" line="5" stmtstart="154" stmtend="298" sqlhandle="0x030005000caa6e4b5b56dd0089a6000000000000000000000000000000000000000000000000000000000000">
update inventory set designation = 'FROZEN' where designation <> 'FROZEN'    
    </frame>
    <frame procname="WMSData.dbo.PostReceipt" line="101" stmtstart="7960" stmtend="10026" sqlhandle="0x0300050071b68341909a680037ae000001000000000000000000000000000000000000000000000000000000">
INSERT INTO [wmsdata].[dbo].[Inventory]
            ([Customer],[Product],[Row],[Rack],[Slot],[Pallets],[Cases],[Net],[ProductDate],[ReceiverNumber],[LotNumber],[ReceiveDate],[StorThru],[Status],[PalletNumber],[CustomerPallet],[StartTime],[StopTime],[BornOnDate],[EnteredBy],[ImportOrManual],[FullPalletQty],[PalletQtyReceived],[CustomerReference],lpnumber,fcgpallet,designation,mark,invoptional1,invoptional2,invoptional3,invoptional4,f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,rsrate)
             select [Customer],[Product],[Row],[Rack],[Slot],[Pallets],[Cases],[Net],[ProductDate],[Receiver],[LotNumber],convert(char(12),getdate(),101) as [ReceiveDate],[StorThru],[Status],[PalletNumber],[CustomerPallet],[StartTime],[StopTime],convert(char(12),getdate(),101) as [BornOnDate],[EnteredBy],[ImportOrManual],[FullPalletQty],cases as [PalletQtyReceived], @r, lpnumber, fcgpallet,designation,mark,invoptional1,invoptional2,invoptional3,invoptional4,f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,rsrate
             from [wmsdata].[dbo].receipts  with (nolock) where palletnum    
    </frame>
    <frame procname="adhoc" line="1" sqlhandle="0x0100050098f50238607f6f0b050000000000000000000000000000000000000000000000000000000000000">
exec dbo.postreceipt '149496','RDAY'    
    </frame>
   </executionStack>
   <inputbuf>
exec dbo.postreceipt '149496','RDAY'   
   </inputbuf>
  </process>
  <process id="process46811bc28" taskpriority="0" logused="22480" waitresource="KEY: 5:72057598307401728 (771c8a402149)" waittime="4364" ownerId="34439550" transactionname="user_transaction" lasttranstarted="2022-07-11T23:22:34.483" XDES="0x5a043d770" lockMode="U" schedulerid="1" kpid="10752" status="suspended" spid="59" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-07-11T23:22:34.497" lastbatchcompleted="2022-07-11T23:22:33.330" lastattention="2022-07-11T23:17:04.210" clientapp="Vizion WMS" hostname="AGROXAWORK02" hostpid="12144" loginname="vizion" isolationlevel="read committed (2)" xactid="34439550" currentdb="5" currentdbname="WMSData" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="WMSData.dbo.PostShipment" line="161" stmtstart="16962" stmtend="17036" sqlhandle="0x0300050069a66001dd5e640021ac000001000000000000000000000000000000000000000000000000000000">
delete from inventory where cases <= 0    
    </frame>
    <frame procname="adhoc" line="1" sqlhandle="0x01000500f2278100b05d3179010000000000000000000000000000000000000000000000000000000000000">
exec Postshipment '257208', 'JJOHNSON'    
    </frame>
   </executionStack>
   <inputbuf>
exec Postshipment '257208', 'JJOHNSON'   
   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <keylock hobtid="72057598307401728" dbid="5" objectname="WMSData.dbo.Inventory" indexname="PK_Inventory" id="lock4e671e880" mode="X" associatedObjectId="72057598307401728">
   <owner-list>
    <owner id="process46811bc28" mode="X" />
   </owner-list>
   <waiter-list>
    <waiter id="process1fb169468" mode="U" requestType="wait" />
   </waiter-list>
  </keylock>
  <keylock hobtid="72057598307401728" dbid="5" objectname="WMSData.dbo.Inventory" indexname="PK_Inventory" id="lock52ec3d280" mode="X" associatedObjectId="72057598307401728">
   <owner-list>
    <owner id="process1fb169468" mode="X" />
   </owner-list>
   <waiter-list>
    <waiter id="process46811bc28" mode="U" requestType="wait" />
   </waiter-list>
  </keylock>
 </resource-list>
</deadlock>

内容的提问来源于stack exchange，提问作者Prajwol