SQL Alter Trigger hangs
I'm trying to change the SQL Server 2000 upgrade trigger but it hangs and hangs and hangs. Why might this be happening and what can I do to get around it? Maybe this is a long trigger?
The 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_:
+3
source to share
1 answer
Is it blocked? Have you checked sysprocesses
or sp_who2
? Follow these steps:
EXEC sp_who2;
(Start it from another window.)
You can see the value in the column BlkBy
for the row that matches the SPID in the original window where you are trying to commit the change. Then you can look at the SPID lines in the same result and see if they work. To determine what exactly you can use:
DBCC INPUTBUFFER(x);
(Replace "x" with the SPID causing the blocking.)
If you determine that this session is a one-off session, you can run:
KILL x;
(Replacing "x" with SPID again.)
However, this should be a last resort - before releasing, KILL
I suggest that you rather find out what it is and why it takes so long. ALTER TRIGGER
should be a relatively fast DDL action, but it needs exclusive access to the underlying objects ... so you can schedule changes for the maintenance window, or at least a period of relatively quiet activity.
+8
source to share