StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL Alter Trigger hanging
text
Body
copied!<p>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?</p> <p>Trigger code is long, but abbreviated below:</p> <pre><code>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_: </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload