SQL Alter Trigger hanging

Question

I'm trying to alter an SQL Server 2000 update trigger, but its hanging and hanging and hanging. Why might this be happening and what can I do to get around it? It is a long trigger, could that be why?

Trigger code is long, but abbreviated below:

ALTER TRIGGER [dbo].[UP_AL_ItemUPCs] ON [dbo].[AL_ItemUPCs] 

FOR UPDATE

  AS

  SET XACT_ABORT ON

  Declare @vError varchar(254)
  Declare @iUpdateCount int

  Select @iUpdateCount = Count ( D.UPCID ) 
    From deleted D

  If @iUpdateCount > 1  
    Begin
       Raiserror ( 'This Table maintains AVANTI data ... mass updates cannot be done, yet' , 16 , 1 )      
       Rollback
       Goto Exit_
    End

  Declare @iUpdateCheck int
  Declare @OldItemNumber varchar(50)
  Declare @ItemNumber varchar(50)
  Declare @OldItemPrefix varchar(5)
  Declare @ItemPrefix varchar(5)
  Declare @OldItemCode varchar(25)
  Declare @ItemCode varchar(25)
  Declare @OldItemSuffix varchar(5)
  Declare @ItemSuffix varchar(5)
  Declare @vOldCustomerCode varchar(24)
  Declare @vNewCustomerCode varchar(24)

  If Update ( [CustomerCode] )
    Begin
      Select @vOldCustomerCode  = D.CustomerCode , @vNewCustomerCode = U.CustomerCode
        From  deleted D
        Inner Join inserted U On D.UPCID = U.UPCID
    End
  Else
    Begin
      Select @vOldCustomerCode  = U.CustomerCode , @vNewCustomerCode =  U.CustomerCode 
          From deleted D
         Inner Join AL_ItemUPCs  U On D.UPCID = U.UPCID
    End

 If Update ( ItemPrefix ) Or Update ( ItemCode ) Or Update ( ItemSuffix ) 
    Begin

    If Update ( ItemPrefix ) 
      Begin
        Select @OldItemPrefix = D.ItemPrefix  ,  @ItemPrefix =  U.ItemPrefix 
          From deleted D
         Inner Join inserted U On D.UPCID = U.UPCID
      End
    Else
      Begin
        Select @OldItemPrefix = U.ItemPrefix  ,  @ItemPrefix =  U.ItemPrefix 
          From deleted D
         Inner Join AL_ItemUPCs  U On D.UPCID = U.UPCID
      End

    If Update ( ItemCode ) 
      Begin
        Select @OldItemCode = D.ItemCode ,  @ItemCode =  U.ItemCode
          From deleted D
         Inner Join inserted U On D.UPCID = U.UPCID
      End
    Else
      Begin
        Select @OldItemCode = IsNull ( U.ItemCode , '' )   ,  @ItemCode =  IsNull ( U.ItemCode , '' )
          From deleted D
         Inner Join AL_ItemUPCs U On D.UPCID = U.UPCID
      End

    If Update ( ItemSuffix ) 
      Begin
        Select @OldItemSuffix= D.ItemSuffix ,  @ItemSuffix =  U.ItemSuffix
          From deleted D
         Inner Join inserted U On D.UPCID = U.UPCID
      End
    Else
      Begin
        Select @OldItemSuffix = IsNull ( U.ItemSuffix , '' )   ,  @ItemSuffix =  IsNull ( U.ItemSuffix , '' )
          From deleted D
         Inner Join AL_ItemUPCs U On D.UPCID = U.UPCID
      End

    Set @OldItemNumber = @OldItemPrefix + '-' + @OldItemCode 
    Set @ItemNumber = @ItemPrefix + '-' + @ItemCode

    If @OldItemNumber = @ItemNumber Goto Skip_ItemUpdate

      Set @iUpdateCheck = 0

      Select @iUpdateCheck = @iUpdateCheck + IsNull ( Count ( recid )  , 0 )
        From [AVANTISERVER\NCL_MASTER].AVANTI.dbo.InventoryTrans C
      Where C.InvItemNumber =@OldItemNumber

--        Inner Join deleted D On C.InvItemNumber = D.ItemPrefix + '-' + D.ItemCode + IsNull ( D.ItemSuffix , '' ) 

      Select @iUpdateCheck = @iUpdateCheck +  IsNull ( Count ( recid )  , 0 ) 
        From [AVANTISERVER\NCL_MASTER].AVANTI.dbo.InventoryLocations C
      Where C.LocItemNumber =@OldItemNumber
          And C.LocQtyOnHand <> 0 

--        Inner Join deleted D On C.LocItemNumber = D.ItemPrefix + '-' + D.ItemCode + IsNull ( D.ItemSuffix , '' ) 

      Select @iUpdateCheck = @iUpdateCheck + IsNull ( Count ( * ) , 0 ) 
        From [AVANTISERVER\NCL_MASTER].AVANTI.dbo.JobItems C
      Where C.Item_Code = @OldItemNumber

--        Inner Join deleted D On C.Item_Code = D.ItemPrefix + '-' + D.ItemCode + IsNull ( D.ItemSuffix , '' ) 

      If @iUpdateCheck > 0 
        Begin
           Raiserror ( ' Item(s) cannot be updated due to associated AVANTI information ! ' , 16 , 1 )
           Rollback
           Goto Exit_
       End

     Update [AVANTISERVER\NCL_MASTER].Avanti.dbo.InventoryHeader Set [AVANTISERVER\NCL_MASTER].Avanti.dbo.InventoryHeader.ItemNumber = @ItemNumber
         From [AVANTISERVER\NCL_MASTER].Avanti.dbo.InventoryHeader AI 
      Where AI.ItemNumber = @OldItemNumber And AI.ColourStyle = @vOldCustomerCode

      Insert Into AL_ItemUPCChanges ( UPCID , ChangeType , FieldName , NewValue , OldValue )
      Select D.UPCID , 'Update' , 'ItemNumber' , @ItemNumber , @OldItemNumber
        From  deleted D
        Inner Join inserted U On D.UPCID = U.UPCID

      Insert Into AL_ItemUPCChanges ( UPCID , ChangeType , FieldName , NewValue , OldValue )
      Select D.UPCID , 'Update' , 'ItemPrefix' , U.ItemPrefix , D.ItemPrefix
        From  deleted D
        Inner Join inserted U On D.UPCID = U.UPCID
     Where D.ItemPrefix <> U.ItemPrefix

      Insert Into AL_ItemUPCChanges ( UPCID , ChangeType , FieldName , NewValue , OldValue )
      Select D.UPCID , 'Update' , 'ItemCode' , U.ItemCode , D.ItemCode
        From  deleted D
        Inner Join inserted U On D.UPCID = U.UPCID
     Where D.ItemCode <> U.ItemCode

      Insert Into AL_ItemUPCChanges ( UPCID , ChangeType , FieldName , NewValue , OldValue )
      Select D.UPCID , 'Update' , 'ItemSuffix' , U.ItemSuffix , D.ItemSuffix
        From  deleted D
        Inner Join inserted U On D.UPCID = U.UPCID
     Where D.ItemSuffix <> U.ItemSuffix


     Update AL_PO Set AL_PO.ItemCode = @ItemNumber
         From deleted I
         Inner Join AL_PO PO On I.UPCID = PO.UPCID
       Where PO.Status <> 'Closed'

    End

Skip_ItemUpdate:


  If Update ( [CustomerCode] )
    Begin

     Update [AVANTISERVER\NCL_MASTER].Avanti.dbo.InventoryHeader Set [AVANTISERVER\NCL_MASTER].Avanti.dbo.InventoryHeader.ColourStyle = U.CustomerCode
        From deleted D
        Inner Join inserted U On D.UPCID = U.UPCID
        Inner Join [AVANTISERVER\NCL_MASTER].Avanti.dbo.InventoryHeader AI On AI.ItemNumber = @ItemNumber  And AI.ColourStyle = @vOldCustomerCode

      Insert Into AL_ItemUPCChanges ( UPCID , ChangeType , FieldName , NewValue , OldValue )
      Select D.UPCID , 'Update' , 'CustomerCode' , U.CustomerCode , D.CustomerCode
        From  deleted D
        Inner Join inserted U On D.UPCID = U.UPCID
     Where D.CustomerCode <> U.CustomerCode

    End

  If Update ( [UPC] )
    Begin

      Insert Into AL_ItemUPCChanges ( UPCID , ChangeType , FieldName , NewValue , OldValue )
      Select D.UPCID , 'Update' , 'UPC' , U.UPC , D.UPC
        From  deleted D
        Inner Join inserted U On D.UPCID = U.UPCID
     Where D.UPC <> U.UPC

    End

  If Update ( [ImageName] )
    Begin

      Insert Into AL_ItemUPCChanges ( UPCID , ChangeType , FieldName , NewValue , OldValue )
      Select D.UPCID , 'Update' , 'ImageName' , U.ImageName , D.ImageName
        From  deleted D
        Inner Join inserted U On D.UPCID = U.UPCID
     Where D.ImageName <> U.ImageName

    End

  If Update ( [Description] )
    Begin

     Update [AVANTISERVER\NCL_MASTER].Avanti.dbo.InventoryHeader Set [AVANTISERVER\NCL_MASTER].Avanti.dbo.InventoryHeader.[Description] = Cast ( U.[Description]  as varchar(60) ),
         [AVANTISERVER\NCL_MASTER].Avanti.dbo.InventoryHeader.OldDescription = Cast ( U.[Description] As varchar(60) )
        From deleted D
        Inner Join inserted U On D.UPCID = U.UPCID
        Inner Join [AVANTISERVER\NCL_MASTER].Avanti.dbo.InventoryHeader AI On AI.ItemNumber = @ItemNumber And AI.ColourStyle = @vNewCustomerCode

     Insert Into AL_ItemUPCChanges ( UPCID , ChangeType , FieldName , NewValue , OldValue )
      Select D.UPCID , 'Update' , 'Description' , U.[Description] , D.[Description]
        From  deleted D
        Inner Join inserted U On D.UPCID = U.UPCID
     Where D.[Description] <> U.[Description]

    End

  If Update ( [Box] )
    Begin

      Insert Into AL_ItemUPCChanges ( UPCID , ChangeType , FieldName , NewValue , OldValue )
Select D.UPCID , 'Update' , 'Box' , U.[Box] , D.[Box]
        From  deleted D
        Inner Join inserted U On D.UPCID = U.UPCID
     Where D.[Box] <> U.[Box]

    End

  If Update ( [QtyPerBox] )
    Begin

      Insert Into AL_ItemUPCChanges ( UPCID , ChangeType , FieldName , NewValue , OldValue )
      Select D.UPCID , 'Update' , 'QtyPerBox' , U.[QtyPerBox] , D.[QtyPerBox]
        From  deleted D
        Inner Join inserted U On D.UPCID = U.UPCID
     Where D.[QtyPerBox] <> U.[QtyPerBox]

    End

  If Update ( [TransFat] )
    Begin

      Insert Into AL_ItemUPCChanges ( UPCID , ChangeType , FieldName , NewValue , OldValue )
      Select D.UPCID , 'Update' , 'TransFat' , U.[TransFat] , D.[TransFat]
        From  deleted D
        Inner Join inserted U On D.UPCID = U.UPCID
     Where D.[TransFat] <> U.[TransFat]

    End

  If Update ( [Length] )
    Begin

     Update [AVANTISERVER\NCL_MASTER].Avanti.dbo.InventoryHeader Set [AVANTISERVER\NCL_MASTER].Avanti.dbo.InventoryHeader.Size1 = U.Length
        From deleted D
        Inner Join inserted U On D.UPCID = U.UPCID
        Inner Join [AVANTISERVER\NCL_MASTER].Avanti.dbo.InventoryHeader AI On AI.ItemNumber = @ItemNumber And AI.ColourStyle = @vNewCustomerCode

      Insert Into AL_ItemUPCChanges ( UPCID , ChangeType , FieldName , NewValue , OldValue )
      Select D.UPCID , 'Update' , 'Length' , U.[Length] , D.[Length]
        From  deleted D
        Inner Join inserted U On D.UPCID = U.UPCID
     Where D.[Length] <> U.[Length]

    End

  If Update ( [Height] )
    Begin

     Update [AVANTISERVER\NCL_MASTER].Avanti.dbo.InventoryHeader Set [AVANTISERVER\NCL_MASTER].Avanti.dbo.InventoryHeader.Size2 = U.Height
        From deleted D
        Inner Join inserted U On D.UPCID = U.UPCID
        Inner Join [AVANTISERVER\NCL_MASTER].Avanti.dbo.InventoryHeader AI On AI.ItemNumber =@ItemNumber And AI.ColourStyle = @vNewCustomerCode

      Insert Into AL_ItemUPCChanges ( UPCID , ChangeType , FieldName , NewValue , OldValue )
      Select D.UPCID , 'Update' , 'Height' , U.[Height] , D.[Height]
        From  deleted D
        Inner Join inserted U On D.UPCID = U.UPCID
     Where D.[Height] <> U.[Height]  

    End

--Similar blocks ommitted for brevity

  If Update ( [FileRevision] )
    Begin

      Insert Into AL_ItemUPCChanges ( UPCID , ChangeType , FieldName , NewValue , OldValue )
      Select D.UPCID , 'Update' , 'FileRevision' , U.[FileRevision] , D.[FileRevision]
        From  deleted D
        Inner Join inserted U On D.UPCID = U.UPCID
     Where D.[FileRevision] <> U.[FileRevision]

    End
Exit_:

Answer 1

Is it being blocked? Did you check sysprocesses or sp_who2? Run the following:

EXEC sp_who2;

(Run it from a different window.)

You might see a value in the BlkBy column for the row that matches the SPID in your original window where you're trying to commit the change. Then you can look at that SPID's row(s) in the same result and see what they're running. To determine what it is exactly you can use:

DBCC INPUTBUFFER(x);

(Replace 'x' with the SPID that is causing the blocking.)

If you determine that this session is disposable, you can run:

KILL x;

(Again replacing 'x' with the SPID.)

However that should be a last resort - before issuing KILL I suggest you rather figure out what it is and why it's taking so long. An ALTER TRIGGER should be a relatively quick DDL action but it does need exclusive access to underlying objects... so you may want to schedule your alter for a maintenance window or at least a period of relatively quiet activity.